Join Create Calendar with parts movements

[Julia2306]

Hi everyone !

I would need your help with a problem that I am unable to solve in Qlik Sense.

I have the following variables in my dataset (you can find a screenshot of the data with "(1) - Dataset Examples" in attachments):

• M_pspn (Part number)
• M_pssn (Serial number)
  We can have several serial numbers for a part number.
• M_etat_dispo (0 if unavailable, 1 if available)
• M_date_heure_mouv (YYYY-MM-DD HH:MM)

My parts have movement histories but not every day. However, I would like to track all parts for every day since 01/01/2021.
I would like to join a calendar that contains all the days since 01/01/2021 and propagate the M_etat_dispo on the days where there is no data. (You can find what i'm trying to do in the "(2) - daily M_etat_dispo")

You can also found in attachment ("(3) - My_code") what i've already done : my current code allows me to retrieve the last movement of the part for the day 

Thanks for all,

Julia

[brunobertels]

Hi Julia 

See this link below on how populate missing date or missing value 

How to populate a sparsely populated field

Generating Missing Data In QlikView

regards 

[Julia2306 · Accepted solution]

Hi @brunobertels ,

Thanks for your answer, it's exactly that. I'm trying on my side.

But it's a bit hard, for me to understand, do you think you could help me for this example?

This is what i've done (But not working with my code) :

MinMaxDate:

Load

Min(M_date_heure_mouv) as MinDate,

Max(M_date_heure_mouv) as MaxDate

Resident TMP4;

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;

Join(TMP4)

Load Date(recno()+$(vMinDate)) as Date_Quotidienne autogenerate vMaxDate - vMinDate;

Table_Finale:

NoConcatenate

Load

Date_Quotidienne,

If(IsNull(M_etat_dispo),Peek(M_etat_dispo), M_etat_dispo) as M_etat_dispo

Resident TMP4

Order by Date_Quotidienne;

Drop table MinMaxDate, TMP4;

Thanks a lot,

Julia

[brunobertels]

Bonjour 

La dim M_date_heure_mouv dans ta table TMP4 est bien un Timestamp ? 

Si le changement d'état ne se fait pas dans un même jour garde le champ Date_Field dans la table TMP4 que tu as créé dans la table TMP2 et encore présente en TMP3 

et sert toi de Date_field au lieu de M_date_heure_mouv 

Si tu peux poster un exemple de ta table TMP4 en excel ? je regarderai 

Cdt 

[Julia2306]

Bonjour @brunobertels ,

Merci beaucoup pour ton retour, ça m'a bcp fait rire que tu me répondes en français 😉

Grâce à tes indications, j'ai finalement réussi après une semaine acharnée, à résoudre ces soucis, milles merci. Il fallait traiter avec le champ DateField.