topic Variable Output based on 2 data source comparisons in QlikView

Variable Output based on 2 data source comparisons

Mon, 25 Apr 2016 21:03:01 GMT

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.

Re: Variable Output based on 2 data source comparisons

trdandamudi — Mon, 25 Apr 2016 21:49:34 GMT

May be as below:

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)

Re: Variable Output based on 2 data source comparisons

swuehl — Mon, 25 Apr 2016 21:51:22 GMT

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;

Re: Variable Output based on 2 data source comparisons

jonas_rezende — Tue, 26 Apr 2016 13:36:35 GMT

Hi, Robyn Jones.

Try:

MS:

LOAD

Project

FROM MS.xls

PS:

LOAD

[Project PS],

[Project_Description],

FROM

PS.XLS

WHERE

EXISTS (Project, [Project PS]);

I hope this helps!

Re: Variable Output based on 2 data source comparisons

Tue, 26 Apr 2016 15:38:40 GMT

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.

Re: Variable Output based on 2 data source comparisons

swuehl — Tue, 26 Apr 2016 16:45:14 GMT

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).

Re: Variable Output based on 2 data source comparisons

Tue, 26 Apr 2016 18:15:03 GMT

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.

Re: Variable Output based on 2 data source comparisons

swuehl — Tue, 26 Apr 2016 18:29:29 GMT

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