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: 
Not applicable

Variable Output based on 2 data source comparisons

I hope I explain this in enough detail..

I have two data sources MS.xls and PS.xls. They both contain a field called Project and Project Description amongst several other fields.

I need the MS data to remain unmodified as its important on its own. When utilizing the PS file, I need to essentially say:

WHERE Project_PS= Project_MS

Pull fields x,y,z from MS.

I tried using Joins but got data from MS that I didn't want in PS and vice versa.

7 Replies
trdandamudi
Master II
Master II

May be as below:

I.

MS_Data:

Load

Project,

[Project Description]

From C:\MS.xls  <---- Update your path and filename here

 

Left Join(MS_Data)

Load

Project,

[Project Description]

From C:\PS.xls  <---- Update your path and filename here


II.

    Where Exists (Project)


swuehl
MVP
MVP

Not exactely sure what you need but maybe like

T1:

LOAD Project

FROM PS.xls;

Projects:

LOAD Project, [Project Description], OtherField

FROM MS.xls

WHERE exists(Project);

DROP TABLE T1;

jonas_rezende
Specialist
Specialist

Hi, Robyn Jones.

Try:

MS:

LOAD

Project

FROM MS.xls

PS:

LOAD

[Project PS],

[Project_Description],

x,

y,

z

FROM

PS.XLS

WHERE

EXISTS (Project, [Project PS]);

I hope this helps!

Not applicable
Author

This one got me the closest but it also removed data that I needed from the PS.xls

So.. I created this sample excel that has two sheets. Pretend each sheet is a separate file. I put data from MS and data from PS in each one. I need the MS data to NOT change at all- when I ask for Project.. I just want Project from MS.

When I pull data from PS- I expect to see every project in PS but the additional info from MS that aligns. IE Where PS 'A' matches something in MS 'Project'  then display all the extra MS columns. Otherwise just give me the PS 'A' value with blanks in all the other columns.

swuehl
MVP
MVP

maybe you just want to join your two tables?

LOAD Project,

          ...

FROM MS.xls;

LEFT JOIN

LOAD Project,

          ....

FROM PS.xls;

Assuming that only Project field name is the same in both tables (otherwise rename the non-key fields), and that there are no duplicate Project entries in PS.xls (Project is a primary key).

Not applicable
Author

The problem here is that i'm getting data from the MS table that I don't want.. for example if MS and PS both have Project 'A' but MS also has Project 'A.2'- I do not want both A and A,2 to come out but that's what's happening.

swuehl
MVP
MVP

Sorry, I don't really understand your requirements.

Earlier you said, I need the MS data to NOT change at all- when I ask for Project.. I just want Project from MS.

To me, this sounds as you want to show also Project A.2