topic Re: problem with table structure in QlikView

problem with table structure

Anonymous — Sat, 10 Nov 2012 12:13:53 GMT

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

Re: problem with table structure

swuehl — Sat, 10 Nov 2012 12:30:06 GMT

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;

Re: problem with table structure

Anonymous — Sat, 10 Nov 2012 13:35:53 GMT

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

Re: problem with table structure

swuehl — Sat, 10 Nov 2012 16:38:27 GMT

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

Re: problem with table structure

Anonymous — Sat, 10 Nov 2012 17:07:39 GMT

THANKS!!