Subject: Assistance Needed with Data Load Issues for KPI Dashboard

[Yasuit]

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

[Clement15]

Hello,

You need to add NoConcatenante here :

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

NoConcatenate

[Tmp_Calendar]:

[Clement15]

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];