topic Re: Exists() in QlikView

Exists()

nihhalmca — Fri, 21 Aug 2015 08:26:06 GMT

Hi All,

1. QVD has Country field.

2. Country field has values respectively USA, INDIA, JAPAN.

3. I want to extract only USA data by optimized way.

I mean using of where exists(). (Not to use where country = 'USA').

Sample Code:

Country:

Load

Country -- it has all countries

From Country.Qvd;

USA:

Load

Country --- expecting here only USA

From Country.qvd where exist ............

Regards,

Nihhal.

Re: Exists()

swuehl — Fri, 21 Aug 2015 08:28:32 GMT

FILTER:

LOAD Country INLINE [

Country

USA

];

LOAD * FROM QVD.qvd (qvd)

WHERE EXISTS(Country);

DROP TABLE FILTER;

Re: Exists()

jagan — Fri, 21 Aug 2015 08:41:04 GMT

Hi Nihhal,

Try like this

Temp:

LOAD

INLINE [

Country

USA];

Data:

LOAD

FROM Datasource

WHERE Exists(Country);

DROP TABLE Temp;

Or you can try Keep

Temp:

LOAD

INLINE [

Country

USA];

Data:

Left Keep(Temp)

LOAD

FROM Datasource;

DROP TABLE Temp;

Re: Exists()

MK_QSL — Fri, 21 Aug 2015 08:43:44 GMT

Temp:

Load * Inline

[

Country

USA

];

Load * From QVDName

Where Exists (Country);

Drop Table Temp;

Re: Exists()

Alvaro_Palacios — Fri, 21 Aug 2015 08:47:12 GMT

Hi Nihhal,

If the exist() doesn't work using the same field name (Country) as in your datasource then try the following code in your script:

TempTable:

LOAD * INLINE [

CountryTemp

USA

];

YourTable:

LOAD * FROM YourDataSource.qvd (qvd)

WHERE EXISTS(CountryTemp,Country);

DROP TABLE TempTable;

Hope this helps

Alvaro P.

Re: Exists()

nihhalmca — Fri, 21 Aug 2015 08:50:24 GMT

Hi Jagan,

First requirement - have to do optimized way if i use keep it should be non-optimized.

QVW has QVD with all countries data please see below sample code.

Country:

Load

Country -- it has all countries

From Country.Qvd;

USA:

Load

Country --- expecting here only USA

From Country.qvd where exist ............

Regards,

Nihhal.

Re: Exists()

jsingh71 — Fri, 21 Aug 2015 08:52:40 GMT

Country:

Load * Inline [

Country

US ];

Tab:

Load *

Where Exists(Country);

LOAD Name,

if(isnull(Grade),0,Grade) as Grade,

Date,

Country

FROM

Sample.xls

(biff, embedded labels, table is Sheet1$);

Re: Exists()

nihhalmca — Fri, 21 Aug 2015 08:52:48 GMT

Hi Manish,

As per your code, small change.

suppose your TEMP table has many countries.

Re: Exists()

nihhalmca — Fri, 21 Aug 2015 08:54:33 GMT

Hi Swuehl,

In my scenario Country has USA, INDIA, JAPAN. However u have taken only USA.

Re: Exists()

nihhalmca — Fri, 21 Aug 2015 08:55:44 GMT

Hi Alvaro,

In my scenario Country has USA, INDIA, JAPAN. However u have taken only USA

Re: Exists()

swuehl — Fri, 21 Aug 2015 08:57:08 GMT

Wasn't this your requirement?

From your original post:

3. I want to extract only USA data by optimized way.

Re: Exists()

Alvaro_Palacios — Fri, 21 Aug 2015 09:16:22 GMT

Hi Nihhal,

If you have only those three countries, you can use the same code above for each country. E.g.

USA:

LOAD * FROM YourDataSource.qvd (qvd)

WHERE EXISTS(Country,'USA');

India:

LOAD * FROM YourDataSource.qvd (qvd)

WHERE EXISTS(Country,'INDIA');

Japan:

LOAD * FROM YourDataSource.qvd (qvd)

WHERE EXISTS(Country,'JAPAN');

Re: Exists()

nihhalmca — Fri, 21 Aug 2015 09:20:18 GMT

Yes Swuehl,

Country filed has USA, INDIA, JAPAN. However you have taken only USA. First I have to take QVD with all countries and second i have to take only USA by using of exists(). Please see my original post i added sample code.

Re: Exists()

jagan — Fri, 21 Aug 2015 09:23:07 GMT

Hi,

If you want multiple countries then use this

Temp:

LOAD

INLINE [

Country

USA

INDIA

JAPAN];

Data:

LOAD

FROM Datasource

WHERE Exists(Country);

DROP TABLE Temp;

Re: Exists()

sasiparupudi1 — Fri, 21 Aug 2015 09:33:12 GMT

Something like this?

Countries:

LOAD Country INLINE [

Country

India

USA

];

NoConcatenate

USA:

LOAD Country Resident Countries where lower(Country)='usa';

let vCountry=Peek('Country',0,'USA'); //---or you can manually set the variable value to USA?

drop Table Countries;

LOAD * FROM QVD.qvd (qvd)

WHERE EXISTS('$(vCountry)');

Re: Exists()

swuehl — Fri, 21 Aug 2015 09:35:35 GMT

Country filed has USA, INDIA, JAPAN. However you have taken only USA. First I have to take QVD with all countries and second i have to take only USA by using of exists(). Please see my original post i added sample code.

As I understand your sample code, both tables (ALL countries and USA table) would be autoconcatenated and resulting in a single table, containing duplicate records for USA.

Is this what you want?

Re: Exists()

nihhalmca — Fri, 21 Aug 2015 09:41:49 GMT

Hi Alvaro,

I tried your logic but no luck, please see below code.

Country:

LOAD Country

FROM

Country.qvd

(qvd);

NoConcatenate

USA:

LOAD Country AS USA

FROM

Country.qvd(qvd) where Exists (Country,'USA');

Drop table Country;

Re: Exists()

nihhalmca — Fri, 21 Aug 2015 09:47:03 GMT

No Jagan, just simple i have one QVD1 with 3 countries and i am taking one more QVD2 it has country field so i am taking USA records itself by using where existes() with QVD1.

Re: Exists()

nihhalmca — Fri, 21 Aug 2015 09:51:56 GMT

Please see my reply to Alvaro.

Re: Exists()

Alvaro_Palacios — Fri, 21 Aug 2015 09:54:05 GMT

Hi Nihhal,

Why are you loading your Country.qvd twice?

In your requirements you said: 3. I want to extract only USA data by optimized way.

According to page 423 in QlikView Reference Manual the following code should work and be an optimized load since you are using the 'where exists' statement. Try this:

Table:

LOAD Country AS USA

FROM Country.qvd (qvd)

where exists(Country,'USA');