1 Reply Latest reply: Sep 5, 2013 4:24 PM by Michael Solomovich RSS

    Apply Maps

      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;

        • Re: Apply Maps
          Michael Solomovich

          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