Need to create a new table by merging 2 tables in qliksense

[Rsaiq]

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

[maxgro · Accepted solution]

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;

[Rsaiq]

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

[maxgro]

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

[Rsaiq]

Thanks a lot  @maxgro  for the explanations.I understood this now perfectly.

Thanks once again....🙂