topic Generating records in between dates in QlikView

Generating records in between dates

dusasuman — Wed, 25 Nov 2020 16:16:04 GMT

Hi community,

Here i am attaching an excel file of sample data. I have a requirement that i need to repeat the same record until it encounters the new date in date field.

In the attached excel file sheet 'Input' the input source data. and what i should get in data model is mentioned in 'Output'.

Please help me.

Re: Generating records in between dates

sunny_talwar — Wed, 06 Jul 2016 14:28:33 GMT

Is this what you want?

Script:

Table:

LOAD Date,

Unit,

Branch,

[Activity Code]

FROM

betweenrecords.xlsx

(ooxml, embedded labels, table is Input);

FinalTable:

NoConcatenate

LOAD Date(Date + IterNo() - 1) as Date,

Unit,

Branch,

[Activity Code]

While Date + IterNo() - 1 < EndDate;

LOAD *,

Date(If(Unit = Previous(Unit), Previous(Date) - 1, Today())) as EndDate

Resident Table

Order By Date desc;

DROP Table Table;

Re: Generating records in between dates

dusasuman — Thu, 07 Jul 2016 06:07:07 GMT

In the source data excel file the 1st sheet is input data and in the 2nd sheet i have mentioned the output that finally i should get..

Re: Generating records in between dates

dusasuman — Thu, 07 Jul 2016 06:25:50 GMT

Hi sunny

thanks for your effort. The result is very close. But the record with 5/1/2016 missing and it is not generating. Also till the end date the records are not generating i.e records are generating till 9/30/2016. Need to complete up to 10/2/2016 with Activity code 'S', Branch Number 10..

Re: Generating records in between dates

dusasuman — Thu, 07 Jul 2016 06:51:17 GMT

Sunny...

Thanks.. Got it, just changed the code. Instead today() i kept Date field and in the while loop included <=..

Got the final results..