Adding a Column based on Matching Conditions in Qlik Sense

[mehdibenayed]

Hi Qlik Sense Community,

I hope you're doing well. I'm currently working on a project involving two tables in Qlik Sense, and I could use some assistance with a specific task. Here are the details:

1. Tables:

   • Table A: Contains a single column with both invoice numbers and delivery note (BL) numbers.
   • Table B: Has two separate columns for invoice numbers and delivery note (BL) numbers.

2. Objective:

   • I need to add a new column to Table A, let's call it "NewColumn."
   • "NewColumn" should have values "yes" or "no" based on the following conditions:
     • If the invoice number in Table A matches the invoice number in Table B.
     • If the delivery note (BL) number in Table A matches the delivery note (BL) number in Table B.
   • Additionally, when a match is found for the invoice number in Table B, all associated delivery note (BL) numbers for that invoice in Table B should also be marked as "yes" in the "NewColumn" of Table A.
   •  

3. Questions:

   • How can I achieve this in Qlik Sense script or expressions?
   • Are there any specific functions or syntax I should be aware of?

Any guidance or examples would be greatly appreciated. Thank you in advance for your assistance!

Best regards,

[Pierrick]

Hi @mehdibenayed,

Here's a possible solution:

//Load your B table first, creating a key with invoice numbers and delivery note
TABLE_B:
Load
    [invoice numbers],
    [delivery note],
    [invoice numbers]&[delivery note] as KEY
from your_data;

//Then load your table A, checking that your number exists in the key previously loaded
TABLE_A:
Load
    your_number,
    if(Exists(KEY,your_number),'Yes','No') as NewColumn
from your_data;

See Exists function 

Pierrick

[mehdibenayed]

Table A contains a single column with entries for both invoice numbers and delivery notes, not concatenated