Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
tomhovens
Contributor II
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
1 Solution

Accepted Solutions
shraddha_g
Partner - Master III
Partner - Master III

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)

View solution in original post

5 Replies
shraddha_g
Partner - Master III
Partner - Master III

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

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

];

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
tomhovens
Contributor II
Contributor II
Author

Many thanks Shraddha and Anil.

How does iterno work?

Anil_Babu_Samineni

Google it !!

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
shraddha_g
Partner - Master III
Partner - Master III