6 Replies Latest reply: Mar 10, 2015 11:00 AM by Lathaa Vishwanthan RSS

    Data transformation

      Let assume I have a record set like below:

      ShopNo Month Year ProfitPercentage

      S1  3   2011   25%

      S2  4   2011   35%

      S3  5   2011   36%

      From the record, we can see shop 1 has only one record for the Month of 3 in 2011 and for other months we need to assume as 0% now we need to auto generate the data for other months too.

      SNo Month Year ProfitPercentage

      S1  1   2011   0%

      S1  2   2011   0%

      S1  3   2011   25%

      S1  4   2011   0%

      S1  5   2011   0%

      S1  6   2011   0%

      S1  7   2011   0%

      S1  8   2011   0%

      S1  9   2011   0%

      S1  10   2011   0%

      S1  11   2011   0%

      S1  12   2011   0%

      Similarly need to generate for other shops too. On top of resultant data we need to do 3-months transformation:

      SNo Month Year ProfitPercentage

      S1  1-3   2011   25%

      S1  2-4   2011   25%

      S1  3-5   2011   25%

      S1  4-6   2011   0%

      S1  5-7   2011   0%

      S1  6-8   2011   0%

      S1  7-9   2011   0%

      S1  8-10  2011   0%

      S1  9-11  2011   0%

      S1  10-12  2011   0%

      S1  11-1   2012   0%

      S1  12-2   2012   0%

      Can anyone help me how to do this kind of data transformation in SQL script in Qlikview

        • Re: Data transformation
          Henric Cronström

          See

          How to populate a sparsely populated field

          Generating Missing Data In QlikView

           

          In these you can find different methods to add "missing" data.

           

          HIC

            • Re: Data transformation

              Thanks for the links its really helpful..

              Can you please share me the code for auto generating the month and year like:

              Year   Month

              2011   01

              2011   02

              .............

              ............

              2011  12

              2012  01

              2012  02

              ............

              ...........

              2012   12

              2013   01

              .............

              2013    12

              • Re: Data transformation

                Got the answer Thanks and I have one more doubt

                 

                 

                FOR y = 2008 to Year(today())

                   FOR m = 1 to 12

                AutogenerateDate:

                       LOAD $(y) &$(m) as Date

                       Autogenerate 1;

                        Next m

                        Next y

                 

                I got result as

                Date

                20081

                20082

                20083

                ......

                ......

                201512

                 

                I want the single digit months should be in mm format like month 1 as 01, month 2 as 02 etc..,

                Date

                200801

                200802

                .........

                .......

                201512

                 

                Help me out please

                  • Re: Data transformation
                    Henric Cronström

                    You can do this is several ways. The first way is:

                      Num(Year,'0000') & Num(Month,'00') as YearMonth

                    This will concatenate a 4-digit year with a two-digit month into a 6-digit string.

                     

                    However, a nicer solution is to use the date format. Then you will have the date serial number for the first day of the month, but formatted any way you want. For instance:

                      Date(MakeDate(Year,Month),'YYYY-MM') as YearMonth      or

                      Date(MakeDate(Year,Month),'YYYYMM') as YearMonth

                    This is my preferred solution. The advantage is that you can use this field in further calculations, like

                      QuarterName(YearMonth)


                    Just make sure to use the same YearMonth both in your real data as in your generated data.

                     

                    HIC

                      • Re: Data transformation

                        Thanks it was really helpful again Kindly help me again please

                         

                        Look this code - I just altered based on my needs but its not working(not computing values for all the YearMonth)-Data structure provided at end....

                         

                        TempTable_Groups:

                        LOAD Rownumber,

                                SumInsured,

                                LossEstimate,

                                Date(BOLEndDate,'YYYYMM') as YearMonth

                           FROM

                          [D:\SeaModeVarSect5.xlsx]

                          (ooxml, embedded labels, table is SeaModeVarSect5);

                         

                           FOR y = 2008 to Year(today())

                           FOR m = 1 to 12

                        AutogenerateDate:

                               LOAD Date(MakeDate($(y),$(m)),'YYYYMM') as YearMonth

                               Autogenerate 1;

                                Next m

                                Next y

                         

                        Groups:

                        NoConcatenate Load YearMonth, Rownumber,

                                  If( IsNull(SumInsured ),0, SumInsured ) as SumInsured,

                                  If( IsNull(LossEstimate ),0, LossEstimate) as LossEstimate

                                  Resident TempTable_Groups

                                  Order By YearMonth ;

                         

                        Drop Table  AutogenerateDate, TempTable_Groups;


                        Sample Data:

                        TempTable_Groups:

                        Rownumber    sumInsured      LossEstimate   YearMonth

                        R1                    1000                100                200802

                        R2                     300                  200               200801

                        AutogenerateDate:

                        YearMonth

                        200801

                        200802

                        ..........

                        201503

                        Expected Final value

                        Rownumber        suminsured     Lossestimate         YearMonth

                        R1                      0                          0                    200801

                        R1                      1000                    100                 200802

                        R1                       0                         0                    200803

                        ..............................................................................

                        ....................................................................................

                        R1                      0                          0                    201503

                        R3                      300                       200                200801

                        R3                      0                           0                  200802

                        R3                       0                         0                    200803

                        ..............................................................................

                        ....................................................................................

                        R3                      0                          0                    201503