Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
yashcena
Contributor III
Contributor III

Need to match a column in two tables

Hi,

I am working with a dataset which has a bug and unfortunately I need to carry on with it. 

The first table has AppName and AppID. However, because of a bug in the source system, there are multiple app names with same AppIDs.

ApplicationIDAppName
1200App01
1200App02
1200App03
1342App04
1349App05
1899App06
1899App99
1650App07
1633App08
1633App65
1340App09
1340App10

 

AppIDs 1200 (3 apps), 1899 (2 apps), and 1340 (2 apps) are problematic and only one app name is valid.

The valid app name is the one which is generating traffic (traffic data is another table)

AppNameTraffic
App022300
App055000
App992100
App097800
App084300

 

So for AppID 1200, only App02 is generating traffic so this is the valid app while the other two (App01 and App03) are invalid.

I have used this logic to flag the valid App Name when they show up in the 2nd table with some traffic 

T1:
LOAD
distinct
ApplicationID,
AppName as AppName2
FROM
[\\xx\Documents\sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left join (T1)

LOAD distinct AppName,1 as flag,
AppName as AppName2

FROM
[\\xx\Documents\sample.xlsx]
(ooxml, embedded labels, table is Sheet2);

 

However, there also some valid apps which are not generating any traffic as of now and hence don't appear in traafic data table. So they are not getting flagged as being a valid app.

Is there any way to resolve this? When there are multiple AppNames for an AppID, I want to just take the AppName which is generating traffic. When there is single AppName and AppID, then I want to take that AppName too (irrespective of whether it appears in traffic data or not)

Any help would be appreciated!

2 Replies
Kushal_Chawda

@yashcena  see the attached

 

Ksrinivasan
Specialist
Specialist

hi,

you can use below script, it will give your result,

hi01:
load
ApplicationID,
AppName
FROM [lib:///New Microsoft Excel Worksheet.xlsx]
(ooxml, embedded labels, table is Sheet1);
left Join
LOAD
AppName,
'1' as Recount
FROM/New Microsoft Excel Worksheet.xlsx]
(ooxml, embedded labels, table is Sheet2);

Join(hi01)
LOAD
ApplicationID,
Count(ApplicationID)as tt
Resident hi01
Group By ApplicationID

;
store hi01 into /New Microsoft Excel Worksheet01.csv](txt);
drop table hi01;


KKK:
LOAD
ApplicationID,
AppName
// Recount,
// tt,
FROM [/New Microsoft Excel Worksheet01.csv]
(txt, utf8, embedded labels, delimiter is ',', msq)
where if(tt=1,tt,Recount)=1;

RESULT:

Ksrinivasan_0-1626291698614.png