HCI DS Update global variable in Dataflow Field mapping

[Former Member]

Hi,

I am trying to convert sourcing ratio file from IBP to validity records to load the data back to IBP. I am using ifthenelse function to see the Key field + Date change to generate group number. I need to increment the Global variable value so that it will be changed.

counter in field mapping expected value

Cust1 PROD1 + Loc1 startdate1 enddate1 $G_VAR1 ( value 1 ) = 1 1

Cust1 PROD1 + Loc2 startdate2 enddate2 $G_VAR1 ( value 1 ) + 1 = 2 2

startdate3 enddate3 $G_VAR1 ( value 1 ) = 1 2

Cust1 PROD1 + Loc1 startdate4 enddate4 $G_VAR1 ( value 1 ) + 1 = 2 3

startdate5 enddate5 $G_VAR1 ( value 1 ) = 1 3.

If i can update global variable in field mapping , this issue can be fixed. When i tried updating Global variable using below logic, it is giving error.

ifthenelse

( c1p1L1date1 <> previous row C1P1L1D1 , $G_VAR1 = $G_VAR1 + 1, $G_VAR1)

Thanks

SG

[jeffrey_kresse]

Global variables can only be set at runtime or in scripts between dataflows. The system is interpreting $G_VAR1 = $G_VAR1 + 1 as a comparison that resolves FALSE, which could be causing your error.

I think you will be able to get what you are looking for with a gen_row_num() function, which will return an incremented integer value with each new row.

A mapping could simply look like this: $G_VAR1 + gen_row_num(). Or if you are starting from a certain number each run you can omit the variable entirely.

Regards,

Jeff K

[Former Member]

Hi Jeff,

Thank you. I have tried GEN_ROW_NUM(). I am getting the below result.

current result expected value

1.Cust1 PROD1 + Loc1 startdate1 enddate1 $G_VAR1 ( value 1 ) = 1 1

2. Cust1 PROD1 + Loc2 startdate2 enddate2 $G_VAR1 ( value 1 ) + 1 = 2 2

3. startdate3 enddate3 $G_VAR1 ( value 1 ) = 1 2

4. startdate4 enddate4 $G_VAR1 ( value 1 ) = 1 2

5. Cust1 PROD1 + Loc1 startdate5 enddate5 $G_VAR1 ( value 1 ) + 1 = 3 3

6. startdate6 enddate6 $G_VAR1 ( value 1 ) = 1 3

Whenever the key combination( Customer/product/Loc/Date) changes , i would like start a new counter and retain the counter value until the next change. counter is working for new combination with gen_row_num(). I need to capture that gen_row_num() and populate the same for next few rows until the key combination changes. I have tried using previous_row_value and it is working for only one row( row3) and keeping same value for the remaining rows( row4). i am not able to get the dynamic previous row value in the target to retain it. I have tried checking all functions but no luck. Any help will be greatly appreciated.

Thanks

SG