How to fill missing values in Qlik Sense

[vish123]

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.

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

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.

[rubenmarin]

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)

[vish123]

Hi Rubenmarin,

Thank you. Let me try

[vchuprina]

Hi, 

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

Regards, 

Vitalii

[vish123]

Hi vchuprina,

it’s excel file.

[vchuprina]

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

[vchuprina]

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