Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik'ers
I load the following table (table A) and want it to convert to table B in the script of Qlikview.
I was thinking about the crosstable function but that doesn't work (with the knowlegde I have)
Does anyone has an idea?
Table A
Item | Project | Quantity | Start Date | End Date |
---|---|---|---|---|
X | A | 1 | 01 - 09- 2017 | 05 - 09 - 2017 |
Y | A | 2 | 01 - 09- 2017 | 05 - 09 - 2017 |
X | B | 2 | 02 - 09- 2017 | 04 - 09 - 2017 |
Table B
Item | Project | Quantity | Date |
---|---|---|---|
X | A | 1 | 01 - 09 - 2017 |
X | A | 1 | 02 - 09 - 2017 |
X | A | 1 | 03 - 09 - 2017 |
X | A | 1 | 04 - 09 - 2017 |
X | A | 1 | 05 - 09 - 2017 |
Y | A | 2 | 01 - 09 - 2017 |
Y | A | 2 | 02 - 09 - 2017 |
Y | A | 2 | 03 - 09 - 2017 |
Y | A | 2 | 04 - 09 - 2017 |
Y | A | 2 | 05 - 09 - 2017 |
X | B | 2 | 02 - 09 - 2017 |
X | B | 2 | 03 - 09 - 2017 |
X | B | 2 | 04 - 09 - 2017 |
Try
Load *,Date(num("Start Date")+iterno()-1) as Date,
while num("Start Date")+IterNo()-1 <=num("End Date");
LOAD
Item,
Project,
Quantity,
"Start Date",
"End Date"
FROM Table A (Use Data Source path of Table A)
Try
Load *,Date(num("Start Date")+iterno()-1) as Date,
while num("Start Date")+IterNo()-1 <=num("End Date");
LOAD
Item,
Project,
Quantity,
"Start Date",
"End Date"
FROM Table A (Use Data Source path of Table A)
Try this?
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff] TT';
Sample:
Load Item, Project, Quantity, Date([Start Date] + IterNo() - 1) as Date
While Date([Start Date] + IterNo() - 1) <= [End Date];
LOAD * Inline [
Item, Project, Quantity, Start Date, End Date
X, A, 1 ,01-09-2017, 05-09-2017
Y, A, 2 ,01-09-2017, 05-09-2017
X, B, 2, 02-09-2017, 04-09-2017
];
Many thanks Shraddha and Anil.
How does iterno work?
Google it !!