problem with table structure

[Anonymous]

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

[swuehl · Accepted solution]

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]

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]

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]

THANKS!!