topic Re: Create Table in script in QlikView

Create Table in script

tomhovens — Thu, 07 Sep 2017 11:36:30 GMT

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

shraddha_g — Thu, 07 Sep 2017 11:41:48 GMT

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)

Anil_Babu_Samineni — Thu, 07 Sep 2017 11:49:41 GMT

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

];

tomhovens — Thu, 07 Sep 2017 12:26:49 GMT

Many thanks Shraddha and Anil.

How does iterno work?

Anil_Babu_Samineni — Thu, 07 Sep 2017 12:56:25 GMT

Google it !!

shraddha_g — Fri, 08 Sep 2017 06:05:45 GMT