Apply Maps

Thu, 05 Sep 2013 19:57:56 GMT

Having trouble posting a question.. Not sure why.

So Yes I know that I didn't post this correctly.

Anyway the issue is that on the second one Month_Number should be the numeric Value for the Month or row() number but it's not. It has the same data as Month.

Not sure what is wrong. I can't attach any files so you can't see the output.

This one works just fine.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Temp_Table:
CrossTable(Month, Quantity_On_Hand, 3)
//this repeats the first three columns in the below pull of data,
// of each month column in the array of eomval
LOAD
     [ITEM NUMBER],
     [WAREHOUSE ID],
     YEAR,
     [QTY ON HAND MONTH 1],
     [QTY ON HAND MONTH 2],
     [QTY ON HAND MONTH 3],
     [QTY ON HAND MONTH 4],
     [QTY ON HAND MONTH 5],
     [QTY ON HAND MONTH 6],
     [QTY ON HAND MONTH 7],
     [QTY ON HAND MONTH 8],
     [QTY ON HAND MONTH 9],
     [QTY ON HAND MONTH 10],
     [QTY ON HAND MONTH 11],
     [QTY ON HAND MONTH 12]
FROM
C:\ProgramData\QlikTech\SourceDocuments\QVD\EOMVAL.qvd
(qvd);

Month_Map: // this will add a month number column so that it can be used to compare for month calculations
MAPPING LOAD
'QTY ON HAND MONTH ' & rowno() as Month_Name,
rowno() as Month_Number
AUTOGENERATE (12); // this will perform the routine 12 times

Data_Table: // this is the table we are creating and will use - make a qvd
Load
     [ITEM NUMBER],
     [WAREHOUSE ID],
     YEAR,
     ApplyMap('Month_Map', Month) as Month_Number, // only number of the line which Matt sorted so it's the number of the month
     Month, // this will be the name of the original field name
     Quantity_On_Hand
Resident Temp_Table;   /// this will pull in the temp table from above but only load these 5 fields

Store Data_Table into EOMVAL_QOH.qvd;

Drop Table Temp_Table;
Drop Table Data_Table;

Second qvw code which has a problem.

Temp_Table:
CrossTable(Month, INV_On_Hand, 3)
//this repeats the first three columns in the below pull of data,
// of each month column in the array of eomval
LOAD
     [ITEM NUMBER],
     [WAREHOUSE ID],
     YEAR,
     [INV MONTH 1],
     [INV MONTH 2],
     [INV MONTH 3],
     [INV MONTH 4],
     [INV MONTH 5],
     [INV MONTH 6],
     [INV MONTH 7],
     [INV MONTH 8],
     [INV MONTH 9],
     [INV MONTH 10],
     [INV MONTH 11],
     [INV MONTH 12]
FROM
C:\ProgramData\QlikTech\SourceDocuments\QVD\EOMVAL.qvd
(qvd);

Month_Map: // this will add a month number column so that it can be used to compare for month calculations
MAPPING LOAD
'INV MONTH ' & rowno() as Month_Name,
rowno() as Month_Number
AUTOGENERATE (12); // this will perform the routine 12 times

Data_Table: // this is the table we are creating and will use - make a qvd
Load
     [ITEM NUMBER],
     [WAREHOUSE ID],
     YEAR,
     ApplyMap('Month_Map', Month) as Month_Number, // only number of the line which Matt sorted so it's the number of the month
     Month, // this will be the name of the original field name
     INV_On_Hand
Resident Temp_Table;   /// this will pull in the temp table from above but only load these 5 fields

Store Data_Table into EOMVAL_INVOnHand_TEST.qvd;

Drop Table Temp_Table;
Drop Table Data_Table;

Re: Apply Maps

Anonymous — Thu, 05 Sep 2013 20:24:39 GMT

Lynda,

If I didn't miss anything - there is no difference between these two cases, so the second should work as well as the first one.

The only question - is it in the same script? If yes - you have two mapping tables with the same name. You cannot drop mapping table. Try to use different name for the second mapping table, maybe it helps.

Regards,

Michael

topic Apply Maps in QlikView

Apply Maps

Re: Apply Maps