Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Current Month means, You want along using Jul Month data in field?
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 ...
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
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...
Transforming to Cross table and playing it from there would be an ideal case to deal this