Insert missing date records

[trdandamudi]

Hi,

I did opened a discussion question on this topic but the logic has changed regarding how the data has to get populated. So i thought opening a new discussion is appropriate.

I need help to insert missing date records. The source table is as below:

Date           Rate       Grade  Status

1/10/2016    215914     1         10

1/10/2016    215914      3         13

1/15/2016    315456      4         15

2/2/2016      456234      5         20

1) In the Script you can have a Start_Date and End_Date  <--- I will hard code the date before running the script for the months required. (MM/DD/YYYY)

2) Now every "Rate" should have a record from Start_Date to End_Date.(It should only insert the missing dates)

For example when you run this script with Start_Date = 1/1/2016 and End_Date = 2/29/2016 you should get the below output which is attached in excel. (For the above four records when you run the script you will get 181 records).

Really appreciate if some one can help or guide me in the right direction.

[sunny_talwar]

See if this helps:

Table:

LOAD * Inline [

Date,          Rate,      Grade,  Status

1/10/2016,    215914,    1,        10

1/10/2016,    215914,      3,        13

1/15/2016,    315456,      4,        15

2/2/2016,      456234,      5,        20

];

LET vMin = Num(MakeDate(2016, 1, 1));

LET vMax = Num(MakeDate(2016, 2, 29));

Temp:

LOAD Date($(vMin) + IterNo() - 1) as Date1,

  Date,

  Rate,

  Grade,

  Status

Resident Table

While $(vMin) + IterNo() - 1 <= $(vMax);

FinalTable:

NoConcatenate

LOAD Date1 as Date,

  Rate,

  If(Date = Date1, Grade, 0) as Grade,

  If(Date = Date1, Status, 0) as Status

Resident Temp;

DROP Tables Table, Temp;

[sunny_talwar]

My bad, did not realize that you just want 1 of the two rows when duplicates are there. Try this:

Table:

LOAD Date,

  Rate,

  FirstValue(Grade) as Grade,

  FirstValue(Status) as Status

Group By Date, Rate;

LOAD * Inline [

Date,          Rate,      Grade,  Status

1/10/2016,    215914,    1,        10

1/10/2016,    215914,      3,        13

1/15/2016,    315456,      4,        15

2/2/2016,      456234,      5,        20

];

LET vMin = Num(MakeDate(2016, 1, 1));

LET vMax = Num(MakeDate(2016, 2, 29));

Temp:

LOAD Date($(vMin) + IterNo() - 1) as Date1,

  Date,

  Rate,

  Grade,

  Status

Resident Table

While $(vMin) + IterNo() - 1 <= $(vMax);

FinalTable:

NoConcatenate

LOAD Date1 as Date,

  Rate,

  If(Date = Date1, Grade, 0) as Grade,

  If(Date = Date1, Status, 0) as Status

Resident Temp;

DROP Tables Table, Temp;

[trdandamudi]

Sunny,

Actually this script is what I want to use. I tried few tests with the data but I am not getting the correct result. When I run the script with the source data that I provided earlier it is working but if I test with the below source data it is not:

Date,           Rate,       Grade,  Status

12/1/2015,    215914,      3,         13

12/1/2015,    215914,     1,         10

12/5/2015,    215914,     1,         10

1/15/2016,    315456,      4,         15

2/2/2016,      456234,      5,         20

];

When I use the above source data it is also creating records for 12/1/2015, 12/5/2015, 1/15/2016 and 2/2/2016 with 0 for both Grade and Status, These records should not be created because these dates are already in the source.

I tried to resolve it but no luck... Hope you can take a look.

Thanks

[sunny_talwar]

Updated the code:

Table:

LOAD Date,

  Rate,

  FirstValue(Grade) as Grade,

  FirstValue(Status) as Status

Group By Date, Rate;

LOAD * Inline [

Date,          Rate,      Grade,  Status

12/1/2015,    215914,      3,        13

12/1/2015,    215914,    1,        10

12/5/2015,    215914,    1,        10

1/15/2016,    315456,      4,        15

2/2/2016,      456234,      5,        20

];

LET vMin = Num(MakeDate(2015, 12, 1));

LET vMax = Num(MakeDate(2016, 2, 29));

Temp:

LOAD Date($(vMin) + IterNo() - 1) as Date1,

  Date,

  Rate,

  Grade,

  Status

Resident Table

While $(vMin) + IterNo() - 1 <= $(vMax);

FinalTable:

NoConcatenate

LOAD Date1 as Date,

  Rate,

  If(Date = Date1, Grade, 0) as Grade,

  If(Date = Date1, Status, 0) as Status

Resident Temp;

Right Join (FinalTable)

LOAD Date,

  Rate,

  Max(Grade) as Grade

Resident FinalTable

Group By Date, Rate;

DROP Tables Table, Temp;

But I think there should be a better way to do this and will hope that someone else might chip in with a better technique to do this.

Best,

Sunny

[trdandamudi]

It is still showing the record with zero (0). See attached .jpg file, It looks like you are almost there but this zero record is messing up.,,,

[sunny_talwar]

This is what I see when I open the application. Did you make any changes before you took the screenshot?

[trdandamudi]

I believe I removed the Group By because I need the multiple rows and I think that is the only change I made. I am using  the code which you cancelled because I do need the multiple rows. Meaning if there are multiple records for the same date then I do need all of them. Attached is the file that I am using.

[sunny_talwar]

Give this a try:

Table:

LOAD * Inline [

Date,          Rate,      Grade,  Status

12/1/2015,    215914,      3,        13

12/1/2015,    215914,    1,        10

12/5/2015,    215914,    1,        10

1/15/2016,    315456,      4,        15

2/2/2016,      456234,      5,        20

];

LET vMin = Num(MakeDate(2015, 12, 1));

LET vMax = Num(MakeDate(2016, 2, 29));

Temp:

LOAD Date($(vMin) + IterNo() - 1) as Date1,

  Date,

  Rate,

  Grade,

  Status

Resident Table

While $(vMin) + IterNo() - 1 <= $(vMax);

FinalTable:

NoConcatenate

LOAD *

Where Flag = 0;

LOAD *,

  If(Rate = Peek('Rate') and Date = Peek('Date') and Grade = 0, 1, 0) as Flag;

LOAD Date1 as Date,

  Rate,

  If(Date = Date1, Grade, 0) as Grade,

  If(Date = Date1, Status, 0) as Status

Resident Temp

Order By Rate, Date1, Grade desc;

DROP Tables Table, Temp;

[trdandamudi]

I opened your dashboard and looked at the output table without making any changes to the code and I still see one extra row. ( Please see the attached .jpg file)

The source data already have the record for 12/5/2015 like below:

Date,           Rate,       Grade,  Status

12/5/2015,    215914,     1,         10

but in the output I am still getting an extra row for the same date like below:

Date,           Rate,       Grade,  Status

12/5/2015,    215914,     0,         0

This is happening only for this record and rest of the records look good.