topic Re: Subject: Assistance Needed with Data Load Issues for KPI Dashboard in App Development

Subject: Assistance Needed with Data Load Issues for KPI Dashboard

Yasuit — Wed, 18 Dec 2024 20:23:32 GMT

Hello Guys,
I am working on creating a KPI dashboard, but loading all my data in the data load editor isn’t going smoothly. Could someone help me achieve the desired Data Model?

The dataset can be found in the Excel file,
and my current script is available in TXT file

Re: Subject: Assistance Needed with Data Load Issues for KPI Dashboard

Clement15 — Thu, 19 Dec 2024 09:40:36 GMT

Hello,

You need to add NoConcatenante here :

let vDateEnd = floor(makedate($(vYearEnd), 12, 31));

NoConcatenate

[Tmp_Calendar]:

Re: Subject: Assistance Needed with Data Load Issues for KPI Dashboard

Clement15 — Thu, 19 Dec 2024 09:53:37 GMT

However, your calendar seems to have a Cartesian product problem. I recommend this code instead.

NoConcatenate

[Tmp_Calendar]:
LOAD
date($(vDateStart) + rowno() - 1) as Date
AUTOGENERATE ($(vDateEnd) - $(vDateStart) + 1);

[Calendar]:
LOAD
floor(Date) as %InvoiceDate,
Date(Date) as Date
1 as _cnt,
if(floor(Date) = $(vToday), 1, 0) as _indToday,
if(year(Date) = year($(vToday)), 1, 0) as _indCY,
if(month(Date) = month($(vToday)), 1, 0) as _indCM,
week(Date) as Week,
month(Date) as Month,
year(Date) as Year,
'Q' & ceil(num(month(Date))/3) as Quarter
RESIDENT [Tmp_Calendar]
ORDER BY Date;
DROP TABLE [Tmp_Calendar];