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]

Wasn't this your requirement?

From your original post:

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

[Alvaro_Palacios]

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

[nihhalmca]

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.

[jagan]

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;

[sasiparupudi1]

Something like this?

Countries:

LOAD Country INLINE [

Country

India

USA

UK

];

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

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

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?

[nihhalmca]

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;

[nihhalmca]

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.

[nihhalmca]

Please see my reply to Alvaro.

[Alvaro_Palacios]

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