Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
That's not correct. CateValue for May-2014 is already and will still be 'Backlog'
Hi Gysbert,
Below is the result I got, please advise.
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;
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
Peek works just fine. Please post a small Qlikview document that demonstrates the problem. We can show you what you're doing wrong then.
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
will it work if we have multiple IDs ?
That depends on your definition of 'it works'. What should the result be?