Exists()

[nihhalmca]

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.

[swuehl · Accepted solution]

FILTER:

LOAD Country INLINE [

Country

USA

];

LOAD * FROM QVD.qvd (qvd)

WHERE EXISTS(Country);

DROP TABLE FILTER;

[jagan]

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;

[MK_QSL]

Temp:

Load * Inline

[

  Country

  USA

];

Load * From QVDName

Where Exists (Country);

Drop Table Temp;

[Alvaro_Palacios]

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.

[nihhalmca]

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.

[jsingh71]

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$);

[nihhalmca]

Hi Manish,

As per your code, small change.

suppose your TEMP table has many countries.

[nihhalmca]

Hi Swuehl,

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

[nihhalmca]

Hi Alvaro,

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