Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
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