topic 'Flattening' a table in QlikView

'Flattening' a table

Mon, 29 Mar 2010 16:08:31 GMT

Hello all,

Searched for the following question on the forum but could't find an example. I have a table (see attachement) with projects and start/end dates for the different phases a project can go through:

Project_Number	Phase	StartDate	Enddate
1	1	1-1-2008	28-2-2008
1	2	1-3-2008	30-9-2008
1	3	1-10-2008	31-12-2008
2	1	1-1-2009	30-6-2009
2	2	1-7-2009	31-12-2009

I want to get a flat table listing the start and end date for a project per phase:

Project_Number	StartDate Phase 1	EndDate Phase 1	StartDate Phase 2	EndDate Phase 2	StartDate Phase 3	EndDate Phase 3
1	1-1-2008	28-2-2008	1-3-2008	30-9-2008	1-10-2008	31-12-2008
2	1-1-2009	30-6-2009	1-7-2009	31-12-2009	-	-

But if I use IF statements in the script to get the date per phase (IF (Phase = 1), DATE(StartDate, 'dd-mm-yyyy'),'') AS StartDate Phase 1, my table will look like this:

Project_Number	StartDate Phase 1	EndDate Phase 1	StartDate Phase 2	EndDate Phase 2	StartDate Phase 3	EndDate Phase 3
1	1-1-2008	28-2-2008
2	1-1-2009	30-6-2009
1			1-3-2008	30-9-2008
2			1-7-2009	31-12-2009
1					1-10-2008	31-12-2008
2					-	-

Re. :'Flattening' a table

Mon, 29 Mar 2010 16:40:37 GMT

Hi,

This is an answer, not the most elegant, but it works...

Hope this helps,

Regards,

Eric.

Re. :'Flattening' a table

johnw — Mon, 29 Mar 2010 18:03:28 GMT

You can do it with a loop. Here's an example, though obviously not for your data. There might be a more elegant way still. I hope there is, because the performance could get pretty bad for this if you're trying to generate a lot of columns.

RawData:
LOAD * INLINE [
Customer, ProductGroup, Status
0001, Screws, Yes
0001, Nails, No
0001, Glue, Yes
0002, Screws, Yes
0002, Nails, No
0002, Glue, Yes
0003, Screws, No
0003, Nails, No
0003, Glue, No
];
ProductGroups:
LOAD fieldvalue('ProductGroup',iterno()) as ProductGroup
AUTOGENERATE 1
WHILE len(fieldvalue('ProductGroup',iterno()))
;
LET PG = peek('ProductGroup',0,'ProductGroups')
;
CustomerPurchases:
LOAD Customer, Status as $(PG)Status
RESIDENT RawData
WHERE ProductGroup = '$(PG)'
;
FOR row = 1 TO noofrows('ProductGroups') - 1
LET PG = peek('ProductGroup',row,'ProductGroups')
;
OUTER JOIN (CustomerPurchases)
LOAD Customer, Status as $(PG)Status
RESIDENT RawData
WHERE ProductGroup = '$(PG)'
;
NEXT

DROP TABLES
RawData
,ProductGroups
;

'Flattening' a table

Wed, 07 Apr 2010 11:23:18 GMT

Sorry for the late reply (easter holiday). The table consists of only about 5000 records, grwoing with a maximum of 200-300 records a year so I'm not too worried about performance. Both your suggestions could work, will try it out this afternoon. Thanks for the input.

'Flattening' a table

Thu, 08 Apr 2010 09:25:55 GMT

Ended up using the first solution because it was easiest to implement. Great advice, thanks.

'Flattening' a table

rwunderlich — Thu, 08 Apr 2010 10:23:00 GMT

I think a simpler solution may be to use a Generic load, which is the inverse of CrossTable load.

Example attached but here's the script as well:

-Rob

data:
LOAD * INLINE [
Project_Number Phase StartDate EndDate
1 1 1-1-2008 28-2-2008
1 2 1-3-2008 30-9-2008
1 3 1-10-2008 31-12-2008
2 1 1-1-2009 30-6-2009
2 2 1-7-2009 31-12-2009
] (delimiter is ' ')
;

GENERIC
LOAD Project_Number, 'Start Date ' & Phase, StartDate
RESIDENT data
;

GENERIC
LOAD Project_Number, 'End Date ' & Phase, EndDate
RESIDENT data
;
DROP Field StartDate, EndDate;

'Flattening' a table

johnw — Thu, 08 Apr 2010 17:46:23 GMT

Wait, what? Why did no one ever tell me about this until now?!

It always seemed strange to me that QlikView would have a crosstable load, but not the inverse. Guess I just needed to look deeper. Thanks, Rob.