4 Replies Latest reply: Apr 18, 2015 9:29 AM by S Satti RSS

    mapping

    S Satti

      ma:

      mapping load * inline

      [Month,Quarter

      Jan,Q1

      Feb,Q1

      Mar,Q1

      Apr,Q2

      May,Q2

      Jun,Q2

      Jul,Q3

      Aug,Q3

      Sep,Q3

      Oct,Q4

      Nov,Q4

      Dec,Q4

      ];

       

      LOAD [Transaction ID],

           Year,

           Month,

           Day,

           [Salesman ID],

           [Product ID],

           [Serial No],

           [ID Customer],

           [List Price],

           Sales,

           [Gross Margin],

           MakeDate(Year,Month,Day) as tdate,

           ApplyMap('ma',Month)

      FROM

      [C:\Users\pandu\Desktop\q . v practece\Data Sources1\Transact.csv]

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

       

      apply map result gives

      1

      2

      3

      4

      5

      6

      7

      8

      9

      10

      11

      12

       

      but my intention is applymap output LIKE THIS

       

      Q1

      Q1

      Q1

      Q2

      Q2

      Q2

      Q3

      Q3

      Q3

      Q4

      Q4

      Q4

       

      ANY ONE HELP ME

       

      REGARDS

      S REDDY

        • Re: mapping
          prabhas v

          Hi,

           

          The output is not possible because only unique values are stored in qlikview.

          so the output like this only

           

          Q1

          Q2

          Q3

          Q4

           

           

          Try this code:

          Ma:

          MAPPING

          load * inline

          [

          month,quarter

          jan,Q1

          feb,Q1

          mar,Q1

          ..     ....

          dec,Q4

           

          ];

          QUARTER:

          LOAD

          Year,

               month,

          applymap ('Ma',month) as new,

               Day

          FROM

          [C:\Users\pandu\Desktop\q . v practece\Data Sources1\Transact.csv]

          PLESE FIND THE below file.if it is useful tick the correct answer.

          • Re: mapping
            Massimo Grossi

            applymap gives

            1

            2

            3

             

            because your Month is a number in Transact table and a month name in ma table

            and when applymap doesn't find a match it returns the 2nd parameter (month number)

             

            try to change the maping table (ma) usng number, 1 not Jan, 2, ot Feb, etc......

            • Re: mapping
              Nuno Faustino

              Hi,

               

              I agree with Massimo but instead of the apply map you could also do something like:

               

              'Q' & Ceil(Month/3) AS Quarter


              Could be simpler in your case.


              Kind regards,

              Nuno