How to generate the Missing Data

[kumar2]

Hi ,

Happy New Year 🙂

We have requirement where we need to generate the Missing Data for the date.

Input:-

Load * Inline [
Date, User,ID
1/1/2020, A, 100
1/1/2020, B, 200
1/2/2020, A, 1000
1/2/2020, B, 2000
1/4/2020, A, 10
1/4/2020, B, 20
1/6/2020, A, 1
1/7/2020, B, 2 

As you can see the above sample data

where for the date "1/3/2020" for the user A and B is missing same for the date"1/5/2020"

For the date"1/6/2020" the User B is missing

For the date "1/7/2020" user A is Missing.

We want the generate the USERID as Zero(0) in the missing dates for both the users

So we need the Missing data for these user

Output should be of

Date, User,ID

1/1/2020, A, 100
1/1/2020, B, 200

1/2/2020, A, 1000

1/2/2020, B, 2000

1/3/2020, A, 0

1/3/2020, B, 0

1/4/2020, A, 10

1/4/2020, B, 20

1/5/2020, A, 0

1/5/2020, B, 0

1/6/2020, A, 1

1/6/2020, B, 0

1/7/2020, A, 0

1/7/2020, B, 2 

Thanks,

Chetan

[Chanty4u]

try this

if(isnull(ID) or len(trim(ID))=0,'0',ID) as missing

or

in front end

modify your expression 

like

if(trim(ID)='-',0,ID)

or

IF(IsNull(ID), 0, ID) AS Missing,

[kumar2]

Thanks @Chanty4u 

In the above you are using to condition to replace the null values

But this not giving the expected output

[Prashant_N · Accepted solution]

Hi Kumar,

Below script might help you

Missing_Date:
Load * Inline [
Date, User,ID
1/1/2020, A, 100
1/1/2020, B, 200
1/2/2020, A, 1000
1/2/2020, B, 2000
1/4/2020, A, 10
1/4/2020, B, 20
1/6/2020, A, 1
1/7/2020, B, 2 ];

Date:
Load
Max(Date) as Maxdate,
Min(Date) as Mindate
Resident Missing_Date;

let vMaxdate = Num(Peek('Maxdate',0,'Date'));
let vMindate = Num(Peek('Mindate',0,'Date'));

Calendar:
LOAD
// $(vMindate) + IterNo() -1 as Num,
Date($(vMindate) + IterNo() -1) as Date2
AutoGenerate 1
While
($(vMindate) + IterNo() -1)<= $(vMaxdate);

drop Table Date;

Left Join(Calendar)
Temp_table:
load Distinct
User as User2
Resident Missing_Date;

mapping_tbl:
mapping load
AutoNumberHash256(Num(Date)&'-'&User) as key,
ID
Resident Missing_Date;

drop Table Missing_Date;

NoConcatenate
Final_tbl:
Load
Date2 as Date,
User2 as User,
ApplyMap('mapping_tbl',AutoNumberHash256(Num(Date2)&'-'&User2),0) as ID
resident Calendar;

drop Table Calendar;

Regards,

Prashant

[Prashant_N · Accepted solution]

Hi Kumar,

This might help you

Regards,

Prashant Naik