Data transformation

[#1]

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

[hic]

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

[#3]

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

[#4]

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

[hic]

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

[#6]

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

  (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

[hic · Accepted solution]

Try

  Date(MonthStart(BOLEndDate),'YYYYMM') as YearMonth

instead of

   Date(BOLEndDate,'YYYYMM') as YearMonth

HIC