topic Re: Concatenate string with Column values in table to resolve variable name in Connectivity & Data Prep

Concatenate string with Column values in table to resolve variable name

Rohan_W — Fri, 22 Sep 2023 12:43:28 GMT

Dear Team,

We have a requirement to calculate future values based upon previous month value for which we have created different variable for each month for which value need to be calculated as shown below,

As you can see, the variable name created in vSep, vOct, vNov and vDec and we need to add it to the table where it is missing(Highlighted Gray) for that particular month for the table shown below,

We have created a column which resolves exact variable name from the existing column values present in the table and we need to resolve it based upon the variable values calculated earlier.

If we use $(concat('v'&[MNTH_END.autoCalendar.Month],'')) it shows missing values.

For Ex - We need to resolve vSep value and it should resolve to 1407 value.

Please suggest to achieve the same.

Re: Concatenate string with Column values in table to resolve variable name

JGMDataAnalysis — Fri, 22 Sep 2023 15:02:41 GMT

How is the variable vMax_Amt defined?

Re: Concatenate string with Column values in table to resolve variable name

Rohan_W — Fri, 22 Sep 2023 15:11:54 GMT

It is created in script by taking Max of AMT column and using peek function to assign it to that variable.

Thanks and Regards,
Rohan Waje
771500663

Re: Concatenate string with Column values in table to resolve variable name

JGMDataAnalysis — Fri, 22 Sep 2023 15:47:01 GMT

In the context of the example I don't understand what the purpose of the Concat function is.
However, you could try the following:

$($(=Concat('v' & [MNTH_END.autoCalendar.Month], ''))) Or directly: $($(='v' & [MNTH_END.autoCalendar.Month]))

Re: Concatenate string with Column values in table to resolve variable name

Rohan_W — Fri, 22 Sep 2023 16:35:16 GMT

The above expression also resulting in null values. Aim here is to resolve the values in variable_list column which has the same name as variable created, ex- vSep should resolve to 1340.

variable list vSep have value 1340 as show below,

The value for vSep in column variable_list should get replaced whit 1340 and similarly it will replace values for other variables vOct,vNov and vDec.

Re: Concatenate string with Column values in table to resolve variable name

JGMDataAnalysis — Fri, 22 Sep 2023 17:12:37 GMT

Try with this:

Pick( Match('v' & [MNTH_END.autoCalendar.Month], 'vJan', 'vFeb', 'vMar', 'vApr', 'vMay', 'vJun', 'vJul', 'vAug', 'vSep', 'vOct', 'vNov', 'vDec') , $(vJan) , $(vFeb) , $(vMar) , $(vApr) , $(vMay) , $(vJun) , $(vJul) , $(vAug) , $(vSep) , $(vOct) , $(vNov) , $(vDec) )

Re: Concatenate string with Column values in table to resolve variable name

Rohan_W — Fri, 22 Sep 2023 17:52:42 GMT

It worked!!! I had created variables for only vSep, vOct, vNov and vDec and modified your script accordingly.

Thank you for your support.

Re: Concatenate string with Column values in table to resolve variable name

JGMDataAnalysis — Fri, 22 Sep 2023 18:29:09 GMT

I included all months on purpose, even knowing that not all variables were defined.
If next year you include any of the variables corresponding to the missing months, you will have to modify the measure because it only considers September, October, November and December.
I am glad that you were able to solve the problem with my collaboration.