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