topic Re: need help with Script in QlikView

need help with Script

Anonymous — Fri, 10 Jul 2015 16:23:27 GMT

join (GlobalData)

load distinct Vendor,

upper(Vendor) as SAccess,

1 as All

Resident GlobalData;

join (GlobalData)

load distinct Vendor,

upper(left(Vendor, 4)) as SAccess ,

4 as All

Resident GlobalData;

join (GlobalData)

load distinct Vendor,

upper(left(Vendor,5)) as SAccess ,

5 as All

Resident GlobalData;

here when I take list box for All, I see only 1 is getting selected and the rest 4 & 5 were greyed out

john

Re: need help with Script

MK_QSL — Fri, 10 Jul 2015 16:28:16 GMT

Provide data please !

Re: need help with Script

Peter_Cammaert — Fri, 10 Jul 2015 16:37:00 GMT

That is because you cannot join the same fields over and over again to the same table. In this code, you try to create columns All and SAccess three times in a row. The first time you succeed, creating a column SAccess and a column All containing value 1 for all rows.

The second and third (ouiter) joins will try to use all fields as matching fields (because they all do exist in the table), producing no matches at all since the All field contains 1 all the time.

Maybe you'ld better explain what you plan to do?

Peter

Re: need help with Script

Anonymous — Fri, 10 Jul 2015 16:55:02 GMT

sure Peter, I have table called globaldata, I' trying to apply section based on Vendor where 1 is to show everything , 4 and 5 is show limited data

john

Re: need help with Script

MK_QSL — Fri, 10 Jul 2015 16:58:25 GMT

GlobalData:

Load * Inline

[

Vendor

ABCDE

XYZMN

PQRST

];

join (GlobalData)

Load Distinct Vendor, Upper(Vendor) as SAccess, 1 as All Resident GlobalData;

join (GlobalData)

Load Distinct Vendor, Upper(Left(Vendor,2)) as SAccess, 2 as All Resident GlobalData;

join (GlobalData)

Load Distinct Vendor, Upper(Left(Vendor,4)) as SAccess, 4 as All Resident GlobalData;

Re: need help with Script

Gabriel — Fri, 10 Jul 2015 17:00:48 GMT

Hi,

Also you're doing OUTER JOIN is that what you wanted to do.

Why not LEFT JOIN?

Re: need help with Script

Anonymous — Fri, 10 Jul 2015 17:12:27 GMT

sorry Manish, this is same as what I have written in the original post. I'll be back with some data, I know its pretty hard to ask these things without data.

as peter suggested, because of that outer join i'm facing this issue, but I tried to perform Noconcatenate here but its throwing illegal combination of prefixes

john

Re: need help with Script

MK_QSL — Fri, 10 Jul 2015 17:19:41 GMT

If you are joining, no need of noconcatenate.

I have provided script to show you that something wrong with your data or something being missed as my sample code is working..

Re: need help with Script

Anonymous — Fri, 10 Jul 2015 18:04:08 GMT

Thanks Manish, could you pls post the sample qvw file so that I'll look into that.

john

Re: need help with Script

MK_QSL — Fri, 10 Jul 2015 18:09:30 GMT

I have provided whole sample script which I used.

Re: need help with Script

Peter_Cammaert — Mon, 13 Jul 2015 07:52:00 GMT

John, forget about my previous post.

The Outer JOIN is essential to your operation as you will use it to concatenate, but it will only work well in a specific circumstance: you can OUTER JOIN to a table that contains (many) other field values but the new records will all have NULL values in the other fields (note that this is based on some guesswork as you're not posting the remainder of your script.)

Manish's example works as a true concatenate because the JOINs do load all field values that were in the original table in the first place. Meaning no NULL values anywhere.

To see what I mean, open Table Viewer (Ctrl-T) and check the contents of table GlobalData.

Also note that every operation like

[LEFT|RIGHT|INNER|OUTER] JOIN (GlobalData)

LOAD DISTINCT ...

RESIDENT GlobalData;

will make GlobalData DISTINCT too, as the DISTINCT keyword propagates to the Target table as well. See the JOIN help topic in QV Desktop Help.

Peter

Re: need help with Script

Anonymous — Wed, 15 Jul 2015 06:29:46 GMT

Thanks Peter, I'll try this as suggested.

john