topic Adding a Column based on Matching Conditions in Qlik Sense in App Development

Adding a Column based on Matching Conditions in Qlik Sense

mehdibenayed — Fri, 15 Nov 2024 21:05:07 GMT

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:

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.
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.
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,

Re: Adding a Column based on Matching Conditions in Qlik Sense

Pierrick — Fri, 02 Feb 2024 11:24:51 GMT

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

Re: Adding a Column based on Matching Conditions in Qlik Sense

mehdibenayed — Fri, 02 Feb 2024 11:30:47 GMT

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