topic Re: Autogenerate from resident data in QlikView

Autogenerate from resident data

Kwame — Sun, 27 Mar 2022 09:09:14 GMT

Greetings guys,

I wish to create records from an exisitng table where the transaction date changes and then combined altogether.

Existing Table:

Date	ID	Amount
24/03/2022	100023	2704.73
24/03/2022	112345	357.09

Desired table:

Date	ID	Amount
24/03/2022	100023	2704.73
24/03/2022	112345	357.09
25/03/2022	100023	2704.73
25/03/2022	112345	357.09

Re: Autogenerate from resident data

Or — Sun, 27 Mar 2022 10:32:12 GMT

Load Date(Date+1) as Date, ID, Amount

Resident YourTable

Where Date = '24/03/2022';

Re: Autogenerate from resident data

Kwame — Mon, 28 Mar 2022 09:53:35 GMT

Thanks @Or , however the requirement is to auto generate with daily date as show below;

Date	ID	Amount
24/03/2022	100023	2704.73
24/03/2022	112345	357.09
25/03/2022	100023	2704.73
25/03/2022	112345	357.09
26/03/2022	100023	2704.73
26/03/2022	112345	357.09
27/03/2022	100023	2704.73
27/03/2022	112345	357.09
28/03/2022	100023	2704.73
28/03/2023	112345	357.09

Re: Autogenerate from resident data

Or — Mon, 28 Mar 2022 10:45:59 GMT

Generally speaking, have a look at this post: https://community.qlik.com/t5/Qlik-Design-Blog/How-to-populate-a-sparsely-populated-field/ba-p/1470637 or this technical brief: https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394

Note that in your case you may want to replace the existing Max(Date) logic with Today(), but otherwise this appears to cover your exact use case.

Insofar as this specific case, you could probably use the code I already posted contained within a loop. An approximation would be:

Let vDate= Date#('24/03/2022');

Do while vDate<= Today()

Load Date(vDate+1) as Date, ID, Amount

Resident YourTable

Where Date = '24/03/2022';

Let vDate = vDate+1;

Loop

Re: Autogenerate from resident data

Kwame — Tue, 05 Apr 2022 07:44:22 GMT

Hi @Or I have been at this for a week now, and still can't get my head around.

Would you be kind enough to shed more light, I'd really appreciate that.

Thank you.

Re: Autogenerate from resident data

Or — Tue, 05 Apr 2022 09:13:50 GMT

I'm afraid it's hard to clarify something if I don't know what it is you don't understand... in this case it's a relatively simple loop which starts on a specific date and then loads the data from that date into the next date, repeating until it reaches today.

Re: Autogenerate from resident data

MarcoWedel — Tue, 05 Apr 2022 19:39:04 GMT

Can you show what you already tried and describe the issues you had in doing so?

Re: Autogenerate from resident data

Hill69 — Thu, 07 Apr 2022 04:10:34 GMT

@Kwame wrote: ChoiceADVANTAGE.com

Greetings guys,

I wish to create records from an exisitng table where the transaction date changes and then combined altogether.

Existing Table:

Date ID Amount

24/03/2022 100023 2704.73

24/03/2022 112345 357.09

Desired table:

Date ID Amount

24/03/2022 100023 2704.73

24/03/2022 112345 357.09

25/03/2022 100023 2704.73

25/03/2022 112345 357.09

In your case you may want to replace the existing Max(Date) logic with Today(), but otherwise this appears to cover your exact use case.