5 Replies Latest reply: Mar 14, 2012 2:07 AM by Perumal Ayyappan RSS

    Mapping of Two Inline Methods in QlikView

    Suresh Rawat

      Hi All,

       

      I want to map Month in calendar table with the DATE in Date table. Both are loaded in the script by inline method. Plz tell me how can i map this in QlikView.

       

       

       

      calendar:
      LOAD * INLINE [
      HALF, QUARTER, MONTH_NUM, MONTH
      H1, Q1, 1, JAN
      H1, Q1, 2, FEB
      H1, Q1, 3, MAR
      H1, Q2, 4, APR
      H1, Q2, 5, MAY
      H1, Q2, 6, JUN
      H2, Q3, 7, JUL
      H2, Q3, 8, AUG
      H2, Q3, 9, SEP
      H2, Q4, 10, OCT
      H2, Q4, 11, NOV
      H2, Q4, 12, DEC
      ];


      Date:
      LOAD * INLINE [
      DATE
      1-1-2010
      4-2-2010
      17-3-2010
      21-4-2010
      27-5-2010
      30-6-2010
      31-7-2010
      19-8-2010
      11-9-2010
      10-10-2010
      17-11-2010
      31-12-2010
      ];

        • Mapping of Two Inline Methods in QlikView
          Perumal Ayyappan

          Hi Suresh

           

          Use This Script

           

          Load HALF, QUARTER, MONTH_NUM, MONTH ,MONTH as Date_Link_Cal;

           

          calendar:

          LOAD * INLINE [

          HALF, QUARTER, MONTH_NUM, MONTH

          H1, Q1, 1, JAN

          H1, Q1, 2, FEB

          H1, Q1, 3, MAR

          H1, Q2, 4, APR

          H1, Q2, 5, MAY

          H1, Q2, 6, JUN

          H2, Q3, 7, JUL

          H2, Q3, 8, AUG

          H2, Q3, 9, SEP

          H2, Q4, 10, OCT

          H2, Q4, 11, NOV

          H2, Q4, 12, DEC

          ];

           

           

          Load  DATE, Month(DATE) as Date_Link_Cal;

          Date:

          LOAD * INLINE [

          DATE

          1-1-2010

          4-2-2010

          17-3-2010

          21-4-2010

          27-5-2010

          30-6-2010

          31-7-2010

          19-8-2010

          11-9-2010

          10-10-2010

          17-11-2010

          31-12-2010

          ];

            • Mapping of Two Inline Methods in QlikView
              Suresh Rawat

              Thanx Perumal,

              It works fine....

                • Mapping of Two Inline Methods in QlikView
                  Suresh Rawat

                  Hi Perumal,

                   

                  When i map a inline data with the excel date, it show me the month wrong. Please tell me the correct way to doing this. I am attaching my Excel file as well. Because of some i am unable to attach my field that is showing in the QlikView but i tell you how it shows the Date field in this.

                   

                  ene/01/2010

                  feb/02/2010

                  mar/03/2010

                  abr/04/2010

                  may/05/2010

                  jun/06/2010

                  jul/07/2010

                  ago/08/2010

                  sep/09/2010

                  oct/10/2010

                  nov/11/2010

                  dic/12/2010


                  In this Jan, Apr,Aug,Dec is shown like this ene, abr, ago, dic.

                   



                  This is my Script:

                  calendar:
                  LOAD * INLINE [
                  HALF, QUARTER, MONTH_NUM, MONTH
                  H1, Q1, 1, Jan
                  H1, Q1, 2, Feb
                  H1, Q1, 3, Mar
                  H1, Q2, 4, Apr
                  H1, Q2, 5, May
                  H1, Q2, 6, Jun
                  H2, Q3, 7, Jul
                  H2, Q3, 8, Aug
                  H2, Q3, 9, Sep
                  H2, Q4, 10, Oct
                  H2, Q4, 11, Nov
                  H2, Q4, 12, Dec
                  ];

                   

                   

                  LOAD Date,
                  Date(Date) as MONTH

                  FROM
                  C:\Users\SureshR\Desktop\Date.xlsx
                  (ooxml, embedded labels, table is Sheet1);

                    • Mapping of Two Inline Methods in QlikView
                      Celambarasan Adhimulam

                      Hi,

                           I think your language setting is in spanish change it or use this set stmt in the start of your load script

                      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';.

                       

                      Celambarasan

                      • Mapping of Two Inline Methods in QlikView
                        Perumal Ayyappan

                        Hi Suresh

                        First U go Setting  ,afetr  change interface lang.  others languages to English .

                        after this below script copy and Paste

                         

                         

                        SET ThousandSep=',';

                        SET DecimalSep='.';

                        SET MoneyThousandSep=',';

                        SET MoneyDecimalSep='.';

                        SET MoneyFormat='$#,##0.00;-$#,##0.00';

                        SET TimeFormat='h:mm:ss TT';

                        SET DateFormat='DD/MM/YYYY';

                        SET TimestampFormat='DD/MM/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';

                         

                        Regards

                        Perumal A