Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
xyz123451
Contributor II
Contributor II

Checking if values in one table exist in another

Hello, I have two tables that I will display in Qliksense.

Table A which displays a list of sessions which have ran (Connected via Oracle)

Category Session_Name Success_Rows Failed_Rows Date
Abc s_m_TA1001 10000   1-Aug-23
Abc s_m_TA1001_1 10000   1-Aug-23
Abc s_m_TA1001_2

9999

 

1 1-Aug-23
Abc s_m_TA1001_2TB 9999 1 1-Aug-23

 

Table B which displays a full list of sessions (Excel file)

S/N Session_Name
1 s_m_TA1001
2 s_m_TA1002
3 s_m_TA1003
4 s_m_TA1004

 

I wish to create a third and fourth column where the third column checks whether the Session_Name exist in Table A and returns either "Yes" or "No" and the fourth column to display the max date in Table A for that particular session name if that session exist in Table A.

Final Table B

S/N Session_Name Has_Run Last_Run
1 s_m_TA1001 Yes 1-Aug-23
2 s_m_TA1002 No  -
3 s_m_TA1003 No  -
4 s_m_TA1004 No  -

 

Will this be possible to be done in the Data Load Editor or via the creation of a calculated field? Thank you!

Labels (3)
1 Solution

Accepted Solutions
BrunPierre
Partner - Master
Partner - Master

Replace the URL of the web file with the name of the table you want to load data from, assuming a successful connection to the Oracle database has been established.

[Table A]:
LOAD Category,
Session_Name,
Success_Rows,
Failed_Rows,
Date#(Date,'DD-MMM-YY') as Date;
SQL SELECT *
FROM YourOracleTableName;

View solution in original post

3 Replies
BrunPierre
Partner - Master
Partner - Master

Hi, as below.

BrunPierre_0-1693636267008.png

[Table A]:
LOAD Category, 
     Session_Name, 
     Success_Rows, 
     Failed_Rows, 
     Date#(Date,'DD-MMM-YY') as Date
  
FROM
[https://community.qlik.com/t5/New-to-Qlik-Sense/Checking-if-values-in-one-table-exist-in-another/td-p/2112463]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

[Final Table B]:
LOAD [S/N], 
     Session_Name,
     If(Exists(Session_Name,Session_Name),'Yes','No') as Has_Run
      
FROM
[https://community.qlik.com/t5/New-to-Qlik-Sense/Checking-if-values-in-one-table-exist-in-another/td-p/2112463]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @2);

Left Join([Final Table B])
LOAD Session_Name,
Date(Max(Date),'DD-MMM-YY') as Last_Run

Resident [Table A]
Group By Session_Name;

DROP Table [Table A];
xyz123451
Contributor II
Contributor II
Author

Hello! Thanks for your reply. I am loading Table A from an Oracle database but I am unsure of how to configure the script to fetch data from the oracle database instead. Not sure if I can just change my from clause of Table A to connect to an Oracle database? Will appreciate any inputs. Thank you!

BrunPierre
Partner - Master
Partner - Master

Replace the URL of the web file with the name of the table you want to load data from, assuming a successful connection to the Oracle database has been established.

[Table A]:
LOAD Category,
Session_Name,
Success_Rows,
Failed_Rows,
Date#(Date,'DD-MMM-YY') as Date;
SQL SELECT *
FROM YourOracleTableName;