Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | - | - |
Hi,
This is an answer, not the most elegant, but it works...
Hope this helps,
Regards,
Eric.
Hi,
This is an answer, not the most elegant, but it works...
Hope this helps,
Regards,
Eric.
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
;
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.
Ended up using the first solution because it was easiest to implement. Great advice, thanks.
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;
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.