Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
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”.
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;
Thank you so much for your help. That works perfectly!