topic Re: Insert missing date records in QlikView

Insert missing date records

trdandamudi — Fri, 04 Mar 2016 21:03:19 GMT

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.

Re: Insert missing date records

sunny_talwar — Fri, 04 Mar 2016 21:18:17 GMT

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;

Re: Insert missing date records

sunny_talwar — Fri, 04 Mar 2016 21:23:40 GMT

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;

Re: Insert missing date records

trdandamudi — Sun, 06 Mar 2016 00:48:04 GMT

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

Re: Insert missing date records

sunny_talwar — Sun, 06 Mar 2016 19:37:27 GMT

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

Re: Insert missing date records

trdandamudi — Sun, 06 Mar 2016 20:02:37 GMT

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.,,,

Re: Insert missing date records

sunny_talwar — Mon, 07 Mar 2016 00:13:07 GMT

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

Re: Insert missing date records

trdandamudi — Mon, 07 Mar 2016 00:41:43 GMT

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.

Re: Insert missing date records

sunny_talwar — Mon, 07 Mar 2016 10:19:07 GMT

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;

Re: Insert missing date records

trdandamudi — Mon, 07 Mar 2016 12:46:58 GMT

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.

Re: Insert missing date records

sunny_talwar — Mon, 07 Mar 2016 13:18:18 GMT

Hopefully no more issues now

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

Temp1:

NoConcatenate

LOAD Date1 as Date,

Rate,

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

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

Resident Temp;

FinalTable:

LOAD *

Where Flag = 0;

LOAD *,

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

Resident Temp1

Order By Rate, Date, Grade desc;

DROP Tables Table, Temp, Temp1;

Re: Insert missing date records

trdandamudi — Mon, 07 Mar 2016 19:32:48 GMT

It is working... In the mean while I am able to do it in little long way by using joins and even that is working. So I ran your code and also mine to compare the result and see if there is a miss. Both the results are matching. I will still do some more testing to make sure everything is perfect.

Thank you very much for your time.

Re: Insert missing date records

sunny_talwar — Mon, 07 Mar 2016 19:40:31 GMT