Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
Hi Rubenmarin,
Thank you. Let me try
Hi,
What is your data source? Database, excel, qvd?
Regards,
Vitalii
Hi vchuprina,
it’s excel file.
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
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