topic Re: Extend table with values in between in App Development

Extend table with values in between

Loading_Screen — Mon, 02 May 2022 13:07:33 GMT

Hi,

I have the following aggregated table:

I want to expand this table such that for each different "I_key" (so for each PO number) the not existing dates in "Date_Ver_Abt" are filled up with the last value from "Ver_Abt".

For example PO 593 has 2 entries for the 27.04.2022. That is fine. Now the 28.04.2022 has no entry for PO 593 so I want to add a line with PO-592, 28.04.2022 PPS (since PPS was the last statement from the previous day 27.04.2022).
Analogue if multiple entries are missing.
I want to fill up the table until the current date.

Any ideas how I can achieve that?

Re: Extend table with values in between

Loading_Screen — Wed, 04 May 2022 15:08:54 GMT

I found some solution but not through the whole process.

I now use the following code:

for i = 570 to 575 step 1
Verantw_Abt$(i):
//Noconcatenate
Load
I_key,
H_created_qlik_date_PO_History as Date_Ver_Abt,
H_toString_PO_History as Ver_Abt
Resident PO_History
where [I_key] = 'PO-' & $(i)
;

MinMaxDate$(i):
Load Min(Date_Ver_Abt) as MinDate_, Max(Date_Ver_Abt) as MaxDate_ resident Verantw_Abt$(i);
Let vMinDate = Peek('MinDate_',-1,'MinMaxDate')-1;
Let vMaxDate = Peek('MaxDate_',-1,'MinMaxDate') ;
Drop Table MinMaxDate$(i);
Join(Verantw_Abt$(i))
Load Date(RecNo()+$(vMinDate)) as Date_Ver_Abt
AutoGenerate vMaxDate - vMinDate;

Verantw_Abt_extended$(i):
NoConcatenate Load Date_Ver_Abt,
If(IsNull(Ver_Abt),Peek(Ver_Abt),Ver_Abt) as Ver_Abt
//If(IsNull(I_key),Peek(I_key)),I_key) as I_key
Resident Verantw_Abt$(i)
Order By Date_Ver_Abt; //So that above values can propagate down
Drop Table Verantw_Abt$(i);

Prod$(i):
Load
Date_Ver_Abt as Date_Ver_Abt_new,
Ver_Abt as Ver_Abt_new,
'PO-'&$(i) as I_key
resident Verantw_Abt_extended$(i)
;
Drop table Verantw_Abt_extended$(i);
//next i

If I don't run the for loop more than one time it works for one PO.
However, if I use the for loop it gives the following error:

I can't figure out what the problem is.

Any ideas?

for i =570 the output is fine and looks like this:

and so on.

Re: Extend table with values in between

Loading_Screen — Thu, 05 May 2022 12:30:20 GMT

Funktioniert nun.
Das Problem war, dass Nulleinträge für vMinDate oder vMaxDate zu Fehler geführt haben.

Re: Extend table with values in between

Loading_Screen — Fri, 06 May 2022 06:50:41 GMT

And in English:
It works now.
Null values for vMinDate and vMaxDate created an error for the calculation of Recno() + nullvalue.

Topic here can be closed
Unfortunate that I did not get any outside help.^^