Joining tables and checking the value exist in another table for each month

[xyz123451]

Hello, I have two tables A and B here and I hope to achieve the final output in Table C. I have no problems doing it in SQL outside of qliksense but I am not too sure about how to do it via the data loading script in qliksense. Will appreciate any help, thank you!

Table A (List of sessions which have ran for each month)

Category	Session_Name	Success_Rows	Failed_Rows	Run_Date	Month_Year
Abc	s_m_TA1001	10000		1-Aug-23	Aug-23
Abc	s_m_TA1001_1	10000		1-Aug-23	Aug-23
Abc	s_m_TA1001_2	4000	1	1-Aug-23	Aug-23
Abc	s_m_TA1001_2TB	4400	1	1-Aug-23	Aug-23
Abc	s_m_TA1001	10000		1-Jul-23	Jul-23
Abc	s_m_TA1001_1	10000		1-Jul-23	Jul-23
Abc	s_m_TA1001_2	3000	2	1-Jul-23	Jul-23
Abc	s_m_TA1001_2TB	9999	1	1-Jul-23	Jul-23
Abc	s_m_TA1001	10000		1-Jun-23	Jun-23
Abc	s_m_TA1001_1	10000		1-Jun-23	Jun-23
Abc	s_m_TA1001_2	9999	1	1-Jun-23	Jun-23
Abc	s_m_TA1001_2TB	9999	1	1-Jun-23	Jun-23

Table B (Session Master List)

S/N	Session_Name
1	s_m_TA1001
2	s_m_TA1001_1
3	s_m_TA1001_2
4	s_m_TA1001_2TB
5	s_m_TA1005
6	s_m_TA1006
7	s_m_TA1007
8	s_m_TA1008
9	s_m_TA1009
10	s_m_TA1010
11	s_m_TA1011

Table C (Final table which is the cross join of Table B and the distinct Month_Year of Table A.  A new Has_Run column needs to be added to check if the Session_Name exist in Table A for each Month_Year)

S/N	Session_Name	Has_Run	Month_Year
1	s_m_TA1001	Yes	Aug-23
2	s_m_TA1001_1	Yes	Aug-23
3	s_m_TA1001_2	Yes	Aug-23
4	s_m_TA1001_2TB	Yes	Aug-23
5	s_m_TA1005	No	Aug-23
6	s_m_TA1006	No	Aug-23
7	s_m_TA1007	No	Aug-23
8	s_m_TA1008	No	Aug-23
9	s_m_TA1009	No	Aug-23
10	s_m_TA1010	No	Aug-23
11	s_m_TA1011	No	Aug-23
12	s_m_TA1001	Yes	Jul-23
13	s_m_TA1001_1	Yes	Jul-23
14	s_m_TA1001_2	Yes	Jul-23
15	s_m_TA1001_2TB	Yes	Jul-23
16	s_m_TA1005	No	Jul-23
17	s_m_TA1006	No	Jul-23
18	s_m_TA1007	No	Jul-23
19	s_m_TA1008	No	Jul-23
20	s_m_TA1009	No	Jul-23
21	s_m_TA1010	No	Jul-23
22	s_m_TA1011	No	Jul-23
23	s_m_TA1001	Yes	Jun-23
24	s_m_TA1001_1	Yes	Jun-23
25	s_m_TA1001_2	Yes	Jun-23
26	s_m_TA1001_2TB	Yes	Jun-23
27	s_m_TA1005	No	Jun-23
28	s_m_TA1006	No	Jun-23
29	s_m_TA1007	No	Jun-23
30	s_m_TA1008	No	Jun-23
31	s_m_TA1009	No	Jun-23
32	s_m_TA1010	No	Jun-23
33	s_m_TA1011	No	Jun-23

[maxgro]

Try the script below

This is the output (table C) in a chart. I don't understand why in your example the S/N column in table C has different values from the table B 

// load tables A and B
A:
LOAD
Category,
Session_Name,
Success_Rows,
Failed_Rows,
Run_Date,
Month_Year
FROM [lib://QlikCommunity]
(html, utf8, embedded labels, table is @1);

B:
LOAD
"S/N",
Session_Name
FROM [lib://QlikCommunity]
(html, utf8, embedded labels, table is @2);

// table C = table B cross join table A
C:
NOCONCATENATE LOAD * RESIDENT B;
JOIN (C) LOAD DISTINCT Month_Year Resident A;

// add to table C the Has_Run column (join A on 2 fields, group by, check the count)

LEFT JOIN (C) LOAD
Session_Name,
Month_Year,
IF(count(Run_Date) >=1, 'YES', 'NO') as Has_Run,
count(Run_Date) as Count_Run
Resident A
GROUP BY Session_Name, Month_Year;

DROP TABLES A, B;

[xyz123451]

Hello! Thanks for the solution. Please ignore the S/N column in Table C as it is a typo on my part.

With regards to your output, can I check why is the Has_run column not indicated as “NO” but “-“ for entries that could not be found in Table A. As your script has the condition to return either “YES” or “NO”. 

[maxgro · Accepted solution]

I think because in the A table some Session_name - Month_Year doesn't exist (example s_m_TA1011).

The   

LEFT JOIN (C) LOAD
Session_Name,
Month_Year,
IF(count(Run_Date) >=1, 'YES', 'NO') as Has_Run,
count(Run_Date) as Count_Run
Resident A
GROUP BY Session_Name, Month_Year;

for s_m_TA1011 doesn't get any row, not a row with 0 in count(Run_Date).

To add the NO you can 

A:
LOAD
Category,Session_Name,Success_Rows,Failed_Rows,Run_Date,Month_Year
FROM [lib://QlikCommunity]
(html, utf8, embedded labels, table is @1);

B:
LOAD
"S/N",Session_Name
FROM [lib://QlikCommunity]
(html, utf8, embedded labels, table is @2);

C:
NOCONCATENATE LOAD * RESIDENT B;
JOIN (C) LOAD DISTINCT Month_Year Resident A;

LEFT JOIN (C) LOAD
Session_Name, Month_Year,
'YES' as Has_Run,     
count(Run_Date) as Count_Run
Resident A
GROUP BY Session_Name, Month_Year;

DROP TABLES A, B;
NOCONCATENATE LOAD *, IF(Has_Run = 'YES', 'YES', 'NO') as New_Has_Run Resident C;
DROP FIELD Has_Run;
RENAME FIELD New_Has_Run to Has_Run;

DROP TABLE C;

[xyz123451]

Thank you so much for your help. That works perfectly!