Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

problem with table structure

Hi,

I've the following table:

Company code          Start Validity Date     End Validity Date

ABC123                         01/01/2007               31/12/2200

I need to produce a table like this:

Company code      Start Validity Date End Validity Date

ABC123                    01/01/2007               31/12/2007

ABC123                    01/01/2008               31/12/2008

ABC123                    01/01/2009               31/12/2009

ABC123                    01/01/2010               31/12/2010

....

How can I produce the table above ? Is there a function in qlikview to build this structure ?

Thanks in advance for your answers

1 Solution

Accepted Solutions
swuehl
MVP
MVP

You should be able to generate your records using a WHILE loop in your script, in a simple setting (start and end date at the beginning and end of a year), it could look like:

INPUT:

LOAD * INLINE [

Company code,          Start Validity Date,     End Validity Date

ABC123,                         01/01/2007,               31/12/2200

];

RESULT:

NoConcatenate LOAD

[Company code],

Date(AddYears([Start Validity Date], iterno()-1)) as [Start Validity Date],

Date(daystart(YearEnd(AddYears([Start Validity Date],iterno()-1)))) as [End Validity Date]

Resident INPUT

while AddYears([Start Validity Date],iterno()-1) <= [End Validity Date];

drop table INPUT;

View solution in original post

4 Replies
swuehl
MVP
MVP

You should be able to generate your records using a WHILE loop in your script, in a simple setting (start and end date at the beginning and end of a year), it could look like:

INPUT:

LOAD * INLINE [

Company code,          Start Validity Date,     End Validity Date

ABC123,                         01/01/2007,               31/12/2200

];

RESULT:

NoConcatenate LOAD

[Company code],

Date(AddYears([Start Validity Date], iterno()-1)) as [Start Validity Date],

Date(daystart(YearEnd(AddYears([Start Validity Date],iterno()-1)))) as [End Validity Date]

Resident INPUT

while AddYears([Start Validity Date],iterno()-1) <= [End Validity Date];

drop table INPUT;

Anonymous
Not applicable
Author

Hi Swuehl,

thanks very much, you gave me a very big help.

Just another thing: if you could explain me how it works , because it's a bit dark for me

Thanks again for you time and help

stefan

swuehl
MVP
MVP

By default, a LOAD statement creates one record in the output table (the table that is created in the QV data model) from one record read from the input table / source.

From the HELP:

"while is a clause used for stating whether a record should be repeatedly read. The same record is read as long as criterion is true. In order to be useful, a while clause must typically include the IterNo( ) function."

So you are creating multiple records from the same input record, for each output record, iterno() will be increased by one and you get record as long as (in your case)

AddYears([Start Validity Date],iterno()-1) <= [End Validity Date]

Hope this helps,

Stefan

Anonymous
Not applicable
Author

THANKS!!