topic Re: Fill the missing data in QlikView in QlikView

Fill the missing data in QlikView

rjn — Mon, 24 Oct 2016 12:42:16 GMT

Dear All,

I am working on to fill the missing data in the existing table in QlikView, below is the scenario, any help would be highly appreciated.

Table A: (Already exists in QlikView Data model)

Should update as highlighted in yellow after reload

Table B:

Regards,

J. Ravichandra Kumar

Re: Fill the missing data in QlikView

Gysbert_Wassenaar — Mon, 24 Oct 2016 12:47:00 GMT

Try:

TableB:

NOCONCATENATE

LOAD

[Month-Year],

If(Len(Trim(ID))=0,Peek(ID),ID) as ID,

If(Len(Trim(CateValue))=0,Peek(CateValue),CateValue) as CateValue

RESIDENT

TableA

ORDER BY [Month-Year]

;

DROP TABLE TableA;

For more information see How to populate a sparsely populated field

Re: Fill the missing data in QlikView

rjn — Mon, 24 Oct 2016 12:55:53 GMT

Hi Gysbert,

This will update only 1st record missing, and other missing records will be blanks, for ex. Only Apr-2014 filled for CateValue field but for May-2014 is still blank.

Regards,

J. Ravichandra Kumar

Re: Fill the missing data in QlikView

Gysbert_Wassenaar — Mon, 24 Oct 2016 13:00:56 GMT

That's not correct. CateValue for May-2014 is already and will still be 'Backlog'

Re: Fill the missing data in QlikView

rjn — Mon, 24 Oct 2016 13:18:53 GMT

Hi Gysbert,

Below is the result I got, please advise.

Re: Fill the missing data in QlikView

MK_QSL — Mon, 24 Oct 2016 13:37:35 GMT

Temp:

Load

Date#([Month-Year],'MMM-YYYY') as [Month-Year],

ID,

CateValue

From .....

NoConcatenate

Load

[Month-Year],

If(IsNull(ID) or Len(Trim(ID))=0, Peek('ID'), ID) as ID,

If(IsNull(CateValue) or Len(Trim(CateValue))=0, Peek('CateValue'), CateValue) as CateValue

Resident Temp

Order By [Month-Year];

Drop Table Temp;

Re: Fill the missing data in QlikView

rjn — Mon, 24 Oct 2016 14:03:04 GMT

Hi Manish,

Thanks for your inputs, I tried as per the above code, it did not work. 😞 . Previous and Peek are not working as we need to fill multiple values to fill, they will work for only 1 value is missing. (my guess).

Please let me know if this can be achieved using FOR ... NEXT loop ?

Regards,

J. Ravichandra Kumar

Re: Fill the missing data in QlikView

Gysbert_Wassenaar — Mon, 24 Oct 2016 14:08:13 GMT

Peek works just fine. Please post a small Qlikview document that demonstrates the problem. We can show you what you're doing wrong then.

Re: Fill the missing data in QlikView

rjn — Mon, 24 Oct 2016 15:09:07 GMT

Hi Gysbert/ Manish,

It worked, there was a Date format issue. will it work if we have multiple IDs ?

Thank you so much for your support on this.

Regards,

J. Ravichandra Kumar

Re: Fill the missing data in QlikView

Gysbert_Wassenaar — Mon, 24 Oct 2016 15:49:52 GMT

will it work if we have multiple IDs ?

That depends on your definition of 'it works'. What should the result be?

Re: Fill the missing data in QlikView

rjn — Mon, 24 Oct 2016 17:03:16 GMT

Hi Gysbert,

Sorry for the brevity.

below is the date we have in our QVD file. The requirement is, we need to show Category "In-Progress" months. i.e., CR ID 'a0CD000000f6R4aMAE' category changed to In-Progress in Feb-2014, so we should count this CR ID in 'In-Progress' status for Feb-2014, Mar-2014, Apr-2014, and May-2014. The same should repeat for other Categories except 'Completed' Status.

Regards,

J. Ravichandra Kumar

Re: Fill the missing data in QlikView

Gysbert_Wassenaar — Mon, 24 Oct 2016 17:23:19 GMT

Perhaps like this:

Temp:

LOAD

[CR ID],

[Month Year],

[CR Category]

FROM

sourcetable;

Result:

LOAD

[CR ID],

Date(AddMonths([Month Year],IterNo()-1),'MMM-YYYY') as [Month Year],

[CR Category]

WHILE

AddMonths([Month Year],IterNo()-1) < NextMY

;

LOAD

[CR ID],

[Month Year],

[CR Category],

If(Previous([CR ID])=[CR ID], Previous([Month Year]),[Month Year]+1) as NextMY

RESIDENT

Temp

ORDER BY [

CR ID], [Month Year] Desc

;

DROP TABLE Temp;