Check value across two tables and return another value

[Abhi_]

Hello,

Please find the attached xlsx as reference for this question.

As shown in the ref, I have two tables (Sheet 2 and Sheet 1) whose key connector is Column B - Code.
Each of these codes have two statuses on Sheet 2 column D  - Delivered or Not delivered
And two status on Sheet 1 column D - Used or Not used

Now what I need to do in Qlik is create a new column (Column E ) on Sheet 2 where

- If Code on Sheet 2 has status "Not Delivered", then it returns "Not Delivered"
- If Code on Sheet 2 has status "Delivered", then it checks the status of the same code on Sheet 1 and returns status "Used" or "Not used"

I have done the same on Sheet 2 in ref already. Thanks for your help!

[MatheusC]

@Abhi_ 
Maybe you can try using the example mapping function:

This is just an example, so modify it to give the desired result, also follow the documentation for a better understanding of the function:

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/MappingFunctio...

MapSheet2:

Mapping
Load
"Column - CodeSheet2",
statusSheet2

YourSheet 2;


TableSheet1:
Load
if(statusSheet1='Delivered',
     ApplyMap('MapSheet2',"Column - CodeSheet1"),statusSheet1) as NewStatus,
"Column - CodeSheet1",
statusSheet1,
.....

YourSheet1;



- Regards, Matheus

[rajuamet]

Hi @Abhi_ , you can use If statement with lookup function.

//------------------------------------------------------------------------------

Sheet1:
Load * Inline [
Reg_Date, Code, Weeknumber, Used or Not
11-26-2024, 10c, 2024 48, Used
];

Sheet2:
Load Reg_Date as Sheet2_Reg_Date,
Code as Sheet2_Code,
Weeknumber as Sheet2_Weeknumber,
[Delivered or not],
If([Delivered or not]='Not Delivered','Not Delivered', lookup('Used or Not', 'Code', Code, 'Sheet1')) as [Final Status Check]
;

Load * Inline [
Reg_Date, Code, Weeknumber, Delivered or not
11-12-2024, 10c, 2024 46, Delivered
08-21-2025, 10a, 2025 34, Not Delivered
];

Exit Script;

//------------------------------------------------------------------------------