Where Exists issue

[#1]

Hi All,

I have three tables one is Customer and others are OpptyCampaigns and LDCampaigns.

Customer table(has 3 crores of records) contains CustomerID, L2ID, L4ID and Campaigns contains CustomerID, Geo etc...

OpptyCampaign and LDCampaign contains CustomerID,GeoCode.

Now my requirement is I have to load Customer table as two separate tables for OpptyCampaigns and LDCampaigns based on CustomerID.

For this I have used below code:

Oppty:

load * from OpptyCampaigns;

load * from Customer where exists(CustomerID);

LD:

load CustomerID as LDCustID,GeoCode as LDGEOCODE from LDCampaigns;

load * from Customer where exists(LDCustID);

But here I am getting issue as LDCustID is not found. Instead of  'Where exists(LDCustID)' if I use 'Where exists(CustomerID)' it's loading all the CustomerID's from First table only.

Even if comment the first table and tried to load only LD table and used 'Where exists(CustomerID)' it's not showing any error but loading 0 records.

Could you please anybody help me here?

Thanks,

Chiru

[#2]

Oppty_tmp:

load * from OpptyCampaigns;

QUALIFY *;

NoConcatenate

Oopty:

load * from Customer where exists(CustomerID);

UNQUALIFY *;

drop table Oppty_tmp;

LD_tmp:

load *from LDCampaigns;

QUALIFY *;

NoConcatenate

LD:

load * from Customer where exists(CustomerID);

unQUALIFY *;

drop table LD_tmp;

[#3]

Hi Naadeep,

Thanks for your reply.

I tried this also but fails to meet my requirement.Because 4 tables shoud be in Data model.

Even if i am trying to load only one table as below I am not getting any issue it's loading 0 records. But there is common CustomerID are there in both tables.

LD:

load CustomerID as LDCustID,GeoCode as LDGEOCODE from LDCampaigns;

load * from Customer where exists(LDCustID);

I am trying to solve this from last 5 days but i didn't get any clue.

Could you please anybody help me on this

Thanks in advance.

Chiru

[maxgro]

I think

Oppty:

load * from OpptyCampaigns;

OpptyCustomer: load * from Customer where exists(CustomerID);

LD:

load CustomerID as LDCustID,GeoCode as LDGEOCODE from LDCampaigns;

LDCustomer: load

    CustomerID as LDCustID,

    <other field of customer table>

from Customer where exists(LDCustID, CustomerID);         

[#5]

Hi Massimo Grossi,

Thanks For Your reply and it's working for me.

Thank you very much for your help.

If you don't mind could you please explain me the difference>

Thanks in advance,

Chiru

[maxgro · Accepted solution]

from Qlik help:

These functions are used when a value from previously loaded records of data is needed for the evaluation of the current record.

exists(field [ , expression ] )

Determines whether a specific field value exists in a specified field of the data loaded so far. Field is a name or a string expression evaluating to a field name. The field must exist in the data loaded so far by the script. Expr is an expression evaluating to the field value to look for in the specified field. If omitted, the current record’s value in the specified field will be assumed.

-----------------------------------------------------------------------------------------------------------------

LD:

load CustomerID as LDCustID,GeoCode as LDGEOCODE from LDCampaigns;

load * from Customer where exists(LDCustID);

this is a

where exists(LDCustID, LDCustID)

first LDCustID is the field, second is LDCustID is the field values to look for (from Customer); but in Customer there isn't any LDCustID field

here you try to load from Customer table the rows where LDCustID (from Customer table) exists in LDCustID field

-----------------------------------------------------------------------------------------------------------------

LD:

load CustomerID as LDCustID,GeoCode as LDGEOCODE from LDCampaigns;

LDCustomer: load

    CustomerID as LDCustID,          // the as LDCustID is to associate the 2 tables, it doesn't matter with exists

    <other field of customer table>

from Customer where exists(LDCustID, CustomerID);   

here you load from Customer table the rows where CustomerID (from Customer table) exists in LDCustID field

[#7]

Thank You Very Much Grossi.