Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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 II
Partner - Master II

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 II
Partner - Master II

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 II
Partner - Master II

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;