Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
anupamajagan
Creator
Creator

Dynamically changing column in Mapping load

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.

 

LOBRegionMetricsJanFebMarAprMayJunJul
ABCNorthPremium10203040405060
ABCSouthPremium11213141415161
ABCEastPremium12223242425262
DEFNorthPremium13233343435363
DEFSouthPremium14243444445464
DEFEastPremium15253545455565
GFHNorthPremium16263646465666
GFHSouthPremium17273747475767
GFHEastPremium18283848485868

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

5 Replies
Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
anupamajagan
Creator
Creator
Author

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 ...

marcus_sommer
MVP & Luminary
MVP & Luminary

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

Peter_Cammaert
Partner
Partner

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...

ychaitanya
Creator III
Creator III

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