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: 
capriconuser
Creator
Creator

matching employees in qlikview

i have server employee list in excel sheet .. and i load data like this 

 

Employee:
LOAD 
 
    EmpID,     
     lower(SUBFIELD([Email ID],'@',1))  AS EmailID     
    
FROM
[data\EmpList\EList*.xlsx]
(ooxml, embedded labels, table is [Sheet - 1]);

 

 

and the other table is 

 

BoxUsage:
LOAD DisplayName as FullName,    
      lower(SUBFIELD(EmailAddress,'@',1)) AS EmailID, 
     left(Usage, Index(Usage, 'B')) as C_InUnits,
     CurrentSize,
     MailMonth,
     MailYear
     FROM
[data\Mail Box Usage\MUsage-*.xlsx]
(ooxml, embedded labels, table is Sheet1);

left Join(Employee)
LOAD
*

Resident BoxUsage;
Drop table BoxUsage;

 

So here 2 table is link with EmailID..  and all data of BoxUsae now in employee table and i want only those emailID in straight table which is in Employeetable ... in BoxUsage some emails starts from upper case but in employee table all emails start with lower case ... so i want only which start from lower case .. 

but now i get data also upper case.

how i resolve it.. 2

3 Replies
MayilVahanan

HI 

Try like below

Employee:
LOAD

EmpID,
lower(SUBFIELD([Email ID],'@',1)) AS EmailID,

1 as EmpEmailFlag

FROM
[data\EmpList\EList*.xlsx]
(ooxml, embedded labels, table is [Sheet - 1]);

 

In straight table, in exp, try like below

Dim: EmailID

Count({<EmpEmailFlag={1}>}urexpression) 

<-- its gives only employee table email info

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
capriconuser
Creator
Creator
Author

@MayilVahanan i am also adding columns from MUsage.. is there any method except putting flag

 

and also now all columns plus MUsage table columns also in employee table 

MayilVahanan

Hi 

Flag is for indication whether the value is coming from this table or not. you can add columns from MUsage table also in the same table

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.