Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey guys.
I have a question , and please help me with some ideas.
I`m importing some info from my database on which I`m creating kpi`s.
The problem is that in my DB some Dates are missing.
So I`m having this :
05/05/2017
06/05/2017
08/05/2017.
For each date I have created some kpi`s (kpiName and kpiNo). In my interface it will look something like this
Date | KpiNo | KpiName | Value |
---|---|---|---|
05/05/2017 | 1 | Test1 | 12 |
05/05/2017 | 2 | Test2 | 15 |
06/05/2017 | 1 | Test1 | 13 |
06/05/2017 | 2 | Test2 | 14 |
08/05/2017 | 1 | Test1 | 11 |
08/05/2017 | 2 | Test2 | 17 |
What I`m trying to do is to add the missing date (07/05/2017) and to put on Value column 0 for each kpi. something like the table below:
Date | KpiNo | KpiName | Value |
---|---|---|---|
05/05/2017 | 1 | Test1 | 12 |
05/05/2017 | 2 | Test2 | 15 |
06/05/2017 | 1 | Test1 | 13 |
06/05/2017 | 2 | Test2 | 14 |
07/05/2017 | 1 | Test1 | 0 |
07/05/2017 | 2 | Test2 | 0 |
08/05/2017 | 1 | Test1 | 11 |
08/05/2017 | 2 | Test2 | 17 |
I`ve achieved to add the missing date by adding this code in Script:
NoConcatenate
MinMax:
Load
ID,
min(HIST_DATE) as MinPeriod,
max(HIST_DATE) as MaxPeriod
Resident IS_acc
Group by
ID;
tempDate:
Load
ID,
date(MinPeriod - 1 + IterNo()) as HIST_DATE
Resident MinMax
while(MinPeriod + IterNo() - 1) <= MaxPeriod;
But the problem is that in my table I can`t see any kpi for 07/05/2017.
Do you have any Idea how can I add the missing rows ?
I can`t post the code here but it will be helpful if you can give me some ideas.
Thank you,
Razvan
Nevermind , I`ve resolved.
I forgot to add in MinMax and tempDate the kpiNo and kpiName columns.
Thanks again.
Hi,
You could just create a Master Calendar, connect it to your data on the date field and use the master calendar fields in the charts. This will allow you to analyze by dates, months, quarters, etc.
Regards,
Andrey