Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have 2 tables in excel as below.
We have to remove all those records from table B that has status success in Table A but in case of muliple same records we have eliminate only one record from those multiple records.
So our output should be like this in qliksense :-
Kindly find data source file in the attchment
Thanks in advance
Try with this script
TableB:
LOAD [contract No], date, amount, rowno() as ID
FROM [lib://E_Downloads/SampleFile.xlsx] (ooxml, embedded labels, table is [Table B]);
LEFT JOIN (TableB)
LOAD [contract No], date, Status
FROM [lib://E_Downloads/SampleFile.xlsx] (ooxml, embedded labels, table is [Table A]);
LEFT JOIN (TableB)
LOAD [contract No], date,
count(Status) as StatusCount
RESIDENT TableB
Where Status = 'Success'
GROUP BY [contract No], date;
R:
NOCONCATENATE LOAD
*,
IF(Status='Success', AUTONUMBER(ID, [contract No] & date)) as AutoNumberField
RESIDENT TableB
ORDER BY [contract No], date, ID;
DROP TABLE TableB;
S:
NOCONCATENATE LOAD
*
RESIDENT R
WHERE
Status <> 'Success'
OR (Status = 'Success' and StatusCount > 1 AND AutoNumberField <> StatusCount) ;
DROP TABLE R;
Try with this script
TableB:
LOAD [contract No], date, amount, rowno() as ID
FROM [lib://E_Downloads/SampleFile.xlsx] (ooxml, embedded labels, table is [Table B]);
LEFT JOIN (TableB)
LOAD [contract No], date, Status
FROM [lib://E_Downloads/SampleFile.xlsx] (ooxml, embedded labels, table is [Table A]);
LEFT JOIN (TableB)
LOAD [contract No], date,
count(Status) as StatusCount
RESIDENT TableB
Where Status = 'Success'
GROUP BY [contract No], date;
R:
NOCONCATENATE LOAD
*,
IF(Status='Success', AUTONUMBER(ID, [contract No] & date)) as AutoNumberField
RESIDENT TableB
ORDER BY [contract No], date, ID;
DROP TABLE TableB;
S:
NOCONCATENATE LOAD
*
RESIDENT R
WHERE
Status <> 'Success'
OR (Status = 'Success' and StatusCount > 1 AND AutoNumberField <> StatusCount) ;
DROP TABLE R;
Hi @maxgro
It is working fine as i expected .Thanks for your prompt reply with solution.
I understood everything just not getting the logic of below code :-
S:
NOCONCATENATE LOAD
*
RESIDENT R
WHERE
Status <> 'Success'
OR (Status = 'Success' and StatusCount > 1 AND AutoNumberField <> StatusCount) ;
Could you please explain me little bit .
Thanks in advance
I'll try to explain.
You can add an exit script after the statement "DROP TABLE TableB" and reload the app
DROP TABLE TableB;
EXIT SCRIPT;
You can see the table R:
Your requirement is
"We have to remove all those records from table B that has status success in Table A but in case of muliple same records we have eliminate only one record from those multiple records."
This is the last resident load in the script, from R to S,
S:
NOCONCATENATE LOAD * RESIDENT R
WHERE
Status <> 'Success'
OR (Status = 'Success' and StatusCount > 1 AND AutoNumberField <> StatusCount) ;
In R:
StatusCount is the number of record with the same contract and date
AutoNumberField is a sequential number inside the contract, date
I keep the record with Status <> Success
OR
I keep the record with Status = Success if the number of record by contract, date is greater than 1 (StatusCount > 1) ; in this case, I also want to eliminate only 1 record (1 of the 2, 3, 4, .... rows with the same contract, date):
if StatusCount = 2, AutonumberField is 1, 2, I delete the record with AutonumberField 2 (AutonumberField<>StatusCount)
if StatusCount = 3, AutonumberField is 1, 2, 3 I delete the record with AutonumberField 3
if StatusCount = 4, AutonumberField is 1, 2, 3, 4 I delete the record with AutonumberField 4
Thanks a lot @maxgro for the explanations.I understood this now perfectly.
Thanks once again....🙂