in QLIK, how do I add data from table B to table A based on conditions in table A

Question

still not completely understanding how to do certain things in qlik.

• I have a table payments, which includes a payment status, and a reference_key
• I have a table payment references which has a reference_key and a reference_number.

payments has like 100 fields in it.

I just want to end up with a field in payments which has the reference number if the status is paid, and is blank if it's not - and I'm finding it oddly hard to do.

in sql it would just be

   select 
        payments.*, 
        case when status='paid' then reference_number else '' end as reference_number
   from 
       payments 
       inner join references on payments.reference_key = references.reference_key

but I'm having a real trouble doing this in qlik - what I tried to do is

    left join (payments)
    load reference_key, reference_number from [references.qvd](qvd)
    where status = 'paid';

but that just gives me field "status" is not found - because of course the status isn't in references.

The only thing I can come up with that works is create rename payments to payments_temp, join to references - create another payments_temp_2 table with a calculated field with if() in that one - then create a new payments table from payments_temp_2 which drops the reference_number column and renames calculated_reference_number to reference_number - which ends up about ~350 lines of code.

clearly I'm doing something wrong - but how do I do it - it seems such a simple thing - and such a common problem?

Note - any number of lines of payments, with all difference statuses - could all be joining to the same reference number

Answer 1

Probably not code length efficient as sql code but the principle is the same. What you can do, in advance, is to prepare the new reference_number field (based on the status) in separate table (without loading the full table). Then join to the main table, drop the orignal reference_number and rename the new field to reference_number

// Join both references and payments tables but load only the required fields
// reference_key, reference_number and status
TempTable:
Load
  Distinct
  reference_key,
  reference_number
from 
 references.qvd (qvd);
 
inner join (TempTable)
 
Load
  Distinct
  status,
  reference_key
from 
 payments.qvd (qvd)
;

// perform the "if" statement calculation
ReferenceNumberStatus:
Load
  reference_number,
  reference_key,
  if(status = 'paid', reference_number, '') as reference_number_status
Resident
  TempTable
;

// we dont need this table anymore
Drop Table TempTable;

// load full payments table
// and join the table with the status calculation
Payments:
Load
  *
from 
 payments.qvd (qvd)
;

join (Payments)

Load
  reference_number,
  reference_number_status
Resident
  ReferenceNumberStatus
;

// we dont need this table anymore
Drop Table ReferenceNumberStatus;

// drop the original reference_number
Drop Field reference_number from Payments;
// rename the new reference_number_status field to reference_number
Rename Field reference_number_status to reference_number;