Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey all I need to Crosstab a table here is a similar example of my current situation noting that in my real case I have more date:
current Table:
ID | Date | Status |
1 | 01/01/2017 | Open |
2 | 01/01/2017 | Intiated |
3 | 01/01/2017 | Closed |
1 | 02/01/2017 | Closed |
2 | 02/01/2017 | Open |
3 | 02/01/2017 | Closed |
Output:
ID | 01/01/2017 | 02/01/2017 |
1 | Open | Closed |
2 | Intiated | Open |
3 | Closed | Closed |
May be try this
Table:
LOAD * INLINE [
ID, Date, Status
1, 01/01/2017, Open
2, 01/01/2017, Intiated
3, 01/01/2017, Closed
1, 02/01/2017, Closed
2, 02/01/2017, Open
3, 02/01/2017, Closed
];
FinalTable:
LOAD Distinct ID
Resident Table;
FOR i = 1 to FieldValueCount('Date')
vField = FieldValue('Date', $(i));
Left Join (FinalTable)
LOAD ID,
Status as [$(vField)]
Resident Table
Where Date = '$(vField)';
NEXT
DROP Table Table;
May be try this
Table:
LOAD * INLINE [
ID, Date, Status
1, 01/01/2017, Open
2, 01/01/2017, Intiated
3, 01/01/2017, Closed
1, 02/01/2017, Closed
2, 02/01/2017, Open
3, 02/01/2017, Closed
];
FinalTable:
LOAD Distinct ID
Resident Table;
FOR i = 1 to FieldValueCount('Date')
vField = FieldValue('Date', $(i));
Left Join (FinalTable)
LOAD ID,
Status as [$(vField)]
Resident Table
Where Date = '$(vField)';
NEXT
DROP Table Table;
Hi,
a (not recommended) script solution would be:
table1:
Generic
LOAD ID,
Date,
Status
FROM [https://community.qlik.com/thread/255167] (html, codepage is 1252, embedded labels, table is @1);
If you just want to present your output table in the front end, then you could use a pivot table, leaving your source table structure as is:
hope this helps
regards
Marco