topic Re: Dynamically changing column in Mapping load in QlikView

Dynamically changing column in Mapping load

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have excel sheet with the below structure(sample data) where for each LOB we have 4 metrics and the values for each month for the current year.

LOB	Region	Metrics	Jan	Feb	Mar	Apr	May	Jun	Jul
ABC	North	Premium	10	20	30	40	40	50	60
ABC	South	Premium	11	21	31	41	41	51	61
ABC	East	Premium	12	22	32	42	42	52	62
DEF	North	Premium	13	23	33	43	43	53	63
DEF	South	Premium	14	24	34	44	44	54	64
DEF	East	Premium	15	25	35	45	45	55	65
GFH	North	Premium	16	26	36	46	46	56	66
GFH	South	Premium	17	27	37	47	47	57	67
GFH	East	Premium	18	28	38	48	48	58	68

For a calculation i need to bring in the current month values alone.

in Mapping load how to make the column to be changing dynamically to the next month ?

somthing like this:

For example current month is feb

so mapping load should be something like this:

Mapping load

LOB & Region,

Feb ---- this should be made dynamic ie automatically it should be Mar next month

from

excel

Any ideas?

Thanks,

Anupama Jagan

Re: Dynamically changing column in Mapping load

Anil_Babu_Samineni — Mon, 12 Feb 2018 13:09:10 GMT

Current Month means, You want along using Jul Month data in field?

Re: Dynamically changing column in Mapping load

Anonymous — Mon, 12 Feb 2018 13:18:52 GMT

No...the lookup should be based on LOB & Region to get the values for the current month column alone from the excel.

if we are hard coding it will be :

Mapping load

LOB & Region,

Feb

from excel

Next month it should be

Mapping load

LOB & Region,

Mar

from excel

Got it? i'm looking for more dynmaic way of changing the column name ...

Re: Dynamically changing column in Mapping load

marcus_sommer — Mon, 12 Feb 2018 13:33:19 GMT

I suggest to transform your crosstable into a normal table - it made many things a lot easier. For a howto see: The Crosstable Load.

- Marcus

Re: Dynamically changing column in Mapping load

Peter_Cammaert — Mon, 12 Feb 2018 14:04:51 GMT

Something like this doesn't force you to unCROSSTABLE your source if you don't need to:

LET vMonthColumnName = Text(Date(Today(), 'MMM'));

MapKey2Value:

MAPPING

LOAD LOB & '|' & Regio AS Key,

[$(vMonthColumnName)] AS Value

FROM ... ();

This works on condition that the MonthNames variable you set at the start of your script contains the same month names as your Excel does. If they do not, you can use something like this to set the correct column name:

LET vMonthColumnName = SubField('Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec',

';', Num(Month(Today())));

BTW What should be done with the Metrics column? If it contains more than one value, you may want to filter using a WHERE clause, aggregate using a GROUP BY clause etc...

Re: Dynamically changing column in Mapping load

ychaitanya — Mon, 12 Feb 2018 14:09:07 GMT

Transforming to Cross table and playing it from there would be an ideal case to deal this