Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I've been setting up some Qlik Sense Cloud sales reporting for us and so far the forum search has been very helpful in answering my question but now I ran into an issue I cannot seem to find any similar cases.
In simplified terms I am reading CSV files with order data:
[Orders]:
Load DISTINCT
"Order Status"
"Date Ordered",
"Order Number",
"Packaging Cost"
From [$(vFileName)](txt, utf8, embedded labels, delimiter is ',', msq);
I have it set up as a loop to always read all files with a certain prefix so that it is easier for the users to just upload another CSV file with the latest orders, instead of maintaining one master file.
As a result the same order might occur in multiple CSV files.
When I run a DISTINCT on the resident table I get rid of most duplicates but there are rare occasions where the "Packaging Cost" has been updated in the source system, so in File A it might be 10 and in File B it might be 15 for the same order number. So DISTINCT thinks it is two different records and I end up with 2 records for the same order.
Is there a way to run something similar like DISTINCT but just on a specific column? In my case I want that there is only 1 record per order number.
I appreciate all help to solve this obstacle.
BR
Julian
If I understood correctly, you need to add :
where not Exists(THEFIELD);
after From
THEFIELD for example is order number
If I understood correctly, you need to add :
where not Exists(THEFIELD);
after From
THEFIELD for example is order number
Thanks @Taoufiq_Zarra
I had to restructure my loading procedure a bit but now it works as expected.
Before my loop to read out all the files I have created a table skeleton with the correct columns and 1 record so that there is something the load statement can compare it to with the Exist statement. Otherwise the first file load always failed because there was nothing loaded yet to compare to.
Great @Notwo
normally it should not return an error since the field is not yet loaded in the memory .
maybe in your context you can load the first file without Exists and start the loop with the second file