Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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
Honored Contributor II

Re: Variable Output based on 2 data source comparisons

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)


MVP
MVP

Re: Variable Output based on 2 data source comparisons

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
Valued Contributor

Re: Variable Output based on 2 data source comparisons

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!

Highlighted
Not applicable

Re: Variable Output based on 2 data source comparisons

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.

MVP
MVP

Re: Variable Output based on 2 data source comparisons

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

Re: Variable Output based on 2 data source comparisons

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.

MVP
MVP

Re: Variable Output based on 2 data source comparisons

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