topic Re: How to fill missing values in Qlik Sense in App Development

How to fill missing values in Qlik Sense

vish123 — Fri, 29 Apr 2022 12:51:34 GMT

Hi Team,

I have below data.

Dataset_Temp:
Load * Inline [
OrderID, Date,CustomerID, Product,Amount
5586, 21-04-22,220, AspireStorm,5
, 21-04-22,340, Everest,12
, 21-04-22,170, RavenHook,13
5587, 21-04-22,210, AspireStorm,5
, 21-04-22,190, RavenHook,4
5588, 22-04-22,300, AspireStorm,7
, 22-04-22,260, Everest,6
, 22-04-22,50, RavenHook,8
];

I have some missing values in OrderID field. I would like to fill these values with respective orderids and tried below but not working.

Data_1:
NoConcatenate
Load if(Isnull(OrderID),Peek(OrderID), OrderID) as OrderID,
Date,
CustomerID,
Product,
Amount
Resident Dataset_Temp
Order by OrderID DESC ;

Drop table Dataset_Temp;

Output still getting as below.

Please suggest.

Re: How to fill missing values in Qlik Sense

rubenmarin — Fri, 29 Apr 2022 18:24:25 GMT

Hi, maybe is loading an empty string (not a null()), you acn try with:

if(Isnull(EmptyIsNull(OrderID)),Peek(OrderID), OrderID)

Or: if(Len(Trim(OrderID))=0,Peek(OrderID), OrderID)

Re: How to fill missing values in Qlik Sense

vish123 — Fri, 29 Apr 2022 18:27:32 GMT

Hi Rubenmarin,

Thank you. Let me try

Re: How to fill missing values in Qlik Sense

vchuprina — Fri, 29 Apr 2022 19:03:03 GMT

Hi,

What is your data source? Database, excel, qvd?

Regards,

Vitalii

Re: How to fill missing values in Qlik Sense

vish123 — Fri, 29 Apr 2022 19:22:04 GMT

Hi vchuprina,

it’s excel file.

Re: How to fill missing values in Qlik Sense

vchuprina — Fri, 29 Apr 2022 19:46:14 GMT

In this case, try following

Dataset_Temp:
LOAD
     Num(OrderID) AS OrderID,
     Date,
     Num(CustomerID) AS CustomerID,
     Product,
     Num(Amount) AS Amount
FROM
[lib://DataFiles/Example.xlsx]
(ooxml, embedded labels, table is Sheet1, filters(
Replace(1, top, StrCnd(null))
));

Regards,

Vitalii

Re: How to fill missing values in Qlik Sense

vchuprina — Fri, 29 Apr 2022 19:52:54 GMT

My result:

It's easy to do in Qlikview in transformation wizard. Qliksense supports Qlikview script, so you can generate necessary code in the Qliview transformation wizard and then copy it to the Qliksense app.

Link:

https://data-flair.training/blogs/transformation-wizard-in-qlikview/

Regards,

Vitalii