Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

tomhovens
New Contributor II

Create Table in script

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

ItemProjectQuantityStart DateEnd Date
XA101 - 09- 201705 - 09 - 2017
YA201 - 09- 201705 - 09 - 2017
XB202 - 09- 201704 - 09 - 2017

Table B

ItemProjectQuantityDate
XA101 - 09 - 2017
XA102 - 09 - 2017
XA103 - 09 - 2017
XA104 - 09 - 2017
XA105 - 09 - 2017
YA201 - 09 - 2017
YA202 - 09 - 2017
YA203 - 09 - 2017
YA204 - 09 - 2017
YA205 - 09 - 2017
XB202 - 09 - 2017
XB203 - 09 - 2017
XB204 - 09 - 2017
Tags (1)
1 Solution

Accepted Solutions
shraddha_g
Honored Contributor III

Re: Create Table in script

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)

5 Replies
shraddha_g
Honored Contributor III

Re: Create Table in script

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)

Re: Create Table in script

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

];

Life is so rich, and we need to respect to the life !!!
tomhovens
New Contributor II

Re: Create Table in script

Many thanks Shraddha and Anil.

How does iterno work?

Re: Create Table in script

Google it !!

Life is so rich, and we need to respect to the life !!!
shraddha_g
Honored Contributor III

Re: Create Table in script

Community Browser