topic Re: Data transformation in Qlik Learning Discussions

Data transformation

Thu, 05 Mar 2015 05:34:58 GMT

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

hic — Thu, 05 Mar 2015 07:08:28 GMT

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

Thu, 05 Mar 2015 09:07:07 GMT

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

Thu, 05 Mar 2015 09:20:47 GMT

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

hic — Thu, 05 Mar 2015 10:04:42 GMT

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

Thu, 05 Mar 2015 11:07:31 GMT

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

Re: Data transformation

hic — Thu, 05 Mar 2015 13:14:55 GMT

Try

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

instead of

Date(BOLEndDate,'YYYYMM') as YearMonth

HIC