topic Re: Duplicate data on a nonexistent date ? in App Development

Duplicate data on a nonexistent date ?

MDE_PF — Wed, 16 Aug 2023 15:39:13 GMT

Hello everyone,

Could you please give me your opinion on the following case, I've tried a few tips found on this site but I'm still struggling to find the solution.

I want to retrieve the odometers of my vehicles at the end of each year (31/12/YYYY).

However, some years, when the vehicle doesn't run at all, no line goes up. So I need to duplicate the previous year's information.

Here's how I proceed:

DIM_PARC_KM:
LOAD
_KEY_Parc_Id AS Parc_ID,
"KM Compteur",
Year("Date") as ANNEE,
Date("Date") as "Date"

FROM DIM_PARC_KM

Then I used this script that I found on this site :

MASTER_CALENDAR: 
//Procedure MasterCalendar:
//1. Extract the Minimum and Maximum dates from a date column in the fact table. From the existing table, you create min and max.
Min_Max:

Load
Min("Date") as MinDate,
Max("Date") as MaxDate
Resident DIM_PARC_KM;

//2. Populate all the dates between the min and max dates using peek function.
Let vMinDate = Peek('MinDate',0,'Min_Max');
Let vMaxDate = Peek('MaxDate',0,'Min_Max');

// Note: after that drop Min_Max temporary table.
Drop Table Min_Max;

//3. Extract all the necessary date columns required for the dashboards
// Pulling dates between Min date and MAx Date

Master_Calendar:
Load *,
Year("Date") as Year,
Month("Date") as Month;

//4. Associate the date column in the fact table to the date column in the master calendar.
Load
Date($(vMinDate) + IterNo() - 1) as "Date"
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

//This is one of the processes for creating a master calendar.

And my last section, where I am trying to fill the data :

DATE_FILLING:
NoConcatenate 
Load "Date,
Parc_ID,
If( IsNull( "KM Compteur" ), Peek( "KM Compteur", -"Date" ), "KM Compteur" ) as "KM Compteur1"

Resident DIM_PARC_KM
Order By "Date" ; // so that above values can be propagated downwards
Drop Table DIM_PARC_KM;

Any ideas about the mistake made?

Any help would be greatly appreciated! 🙂

Thanks !

Re: Duplicate data on a nonexistent date ?

edwin — Thu, 17 Aug 2023 17:52:25 GMT

is "KM Compteur" the KMs travelled for the date or the odometer reading for the date. those are two diff things. the odometer reading is the accumulation of the KMs travelled. the solution i suggest is to create a bridge between your fact and your calendar that when a user selects a date - it is associated to all the KMs prior to that date and you can just sum(KMs) to get the odometer reading. or if your fact contains the odometer reading for that date (and in some dates you dont have an odometer reading) i would associate the date to the last odometer reading prior or equal to that date so your expression would just be only(ODOMETERREADING)

Re: Duplicate data on a nonexistent date ?

edwin — Thu, 17 Aug 2023 18:09:22 GMT

the following assumes you have odometer reading:

NoConcatenate KMs: load *, ID & '|' & date_read as key inline [ ID, odometer_reading, date_read 1, 50000, 8/1/2023 1, 51000, 8/2/2023 1, 52500, 8/3/2023 1, 60000, 8/10/2023 ]; NoConcatenate Calendar: load date(date('8/1/2023')+iterno()-1) as date while date(date('8/1/2023')+iterno()-1)<=date('8/10/2023'); load 1 AutoGenerate(1); NoConcatenate tmpBridge: load key, ID, date_read Resident KMs; inner join (tmpBridge) load date Resident Calendar; NoConcatenate tmpBridge2: load ID, key, date_read, date Resident tmpBridge where date >= date_read; drop table tmpBridge; inner join (tmpBridge2) load ID, date(max(date_read)) as date_read, date Resident tmpBridge2 group by ID, date; NoConcatenate Bridge: load key, date Resident tmpBridge2; drop table tmpBridge2;

this is the DM:

this is a sample output:

Re: Duplicate data on a nonexistent date ?

edwin — Thu, 17 Aug 2023 18:10:16 GMT

since there are no readings from 8/4 to 8/9 then the odometer reading didnt change - stays at what was read in 8/3