Subset Ratio help

[gauthamchilled]

I have two tables, client and department.

I wanted to load both tables but dont want to load records for the department where the client exist in the department.

just load the clients and its corresponding departments.

I can load using left join. but i tried to load both tables with exist function but couldnt make subset ratio 100%

can any one help?

attached the example

[avinashelite]

Please attach the sample with complete description

[gauthamchilled]

sample attached already..just need to know how to make the tables with 100% subset ratio

[gauthamchilled]

any help

[avinashelite]

could  you please post your script , I don't have the QV license

[gauthamchilled]

DeptTemp:

LOAD

  Department_ID& '-' & Office_ID& '-' & Country & '-' & Period as DeptKey,

  //Country,

   //  Department_ID,

     Department_Name,

     Geography,

   //  Office_ID,

     Office_Name,

  //   Period,

     Region_Name

FROM

(qvd);

NoConcatenate

Dept:

Load *

Resident DeptTemp

Where Exists (DeptKey);

DROP Table DeptTemp;

Client:

LOAD

Producing_Dept& '-' & Office_ID & '-' & Country & '-' & Period as DeptKey,

      Affinity_Client_Flag,

     Alignment_Flag,

     C_Client_Source_System_ID,

     Client_ID,

     Client_Industry,

     Client_Industry_ID,

     Client_Motor_Fleet_Flag,

     Client_Name

FROM

(qvd)

Where Country='Australia';

[avinashelite]

Gautham, I am not getting what your trying to do here

Dept:

Load *

Resident DeptTemp

Where Exists (DeptKey);

since you don't have any condition it will not reduce any data from the DeptTemp table ..it will load all the data .

if you want to limit it for only for the Country='Australia' then try like this

Dept:

Load *

Resident DeptTemp

Where Exists (DeptKey) and Country='Australia' ;// don't forget to un comment the Country column in the main table

[gauthamchilled]

Avinash,

Thanks for the reply.

My question is i want something like below.

Dept:

Deptkey,Department

1,IT

2,Sales

3,Hr

Client:

Deptkey,Department,ClientID,Clientname

2,Sales,2500,ABV

3,HR,3243,SEZ

Resulting table:

Client:

Deptkey,Department,ClientID,Clientname

2,Sales,2500,ABV

3,HR,3243,SEZ

Dept:

Deptkey,Department

2,Sales

3,Hr

Bottomoline:

I want 100%subsetratio in my datamodel for both the tables. How can i achieve it using exists function?

[avinashelite]

try like this

Client:

LOAD

Producing_Dept& '-' & Office_ID & '-' & Country & '-' & Period as DeptKey,

Producing_Dept& '-' & Office_ID & '-' & Country & '-' & Period as Temp_Key,

      Affinity_Client_Flag,

     Alignment_Flag,

     C_Client_Source_System_ID,

     Client_ID,

     Client_Industry,

     Client_Industry_ID,

     Client_Motor_Fleet_Flag,

     Client_Name

FROM

(qvd)

Where Country='Australia';

DeptTemp:

LOAD

  Department_ID& '-' & Office_ID& '-' & Country & '-' & Period as DeptKey,

  //Country,

   //  Department_ID,

     Department_Name,

     Geography,

   //  Office_ID,

     Office_Name,

  //   Period,

     Region_Name

FROM

(qvd);

NoConcatenate

Dept:

Load *

Resident DeptTemp

Where Exists (Temp_Key,DeptKey);

DROP Table DeptTemp;

Drop field Temp_Key;

or

Client:

LOAD

Producing_Dept& '-' & Office_ID & '-' & Country & '-' & Period as DeptKey,

      Affinity_Client_Flag,

     Alignment_Flag,

     C_Client_Source_System_ID,

     Client_ID,

     Client_Industry,

     Client_Industry_ID,

     Client_Motor_Fleet_Flag,

     Client_Name

FROM

(qvd)

Where Country='Australia';

DeptTemp:

LOAD

  Department_ID& '-' & Office_ID& '-' & Country & '-' & Period as DeptKey,

  //Country,

   //  Department_ID,

     Department_Name,

     Geography,

   //  Office_ID,

     Office_Name,

  //   Period,

     Region_Name

FROM

(qvd)

where Exists(DeptKey,Department_ID& '-' & Office_ID& '-' & Country & '-' & Period);

[kamal_sanguri]

I have corrected your script.. pls reload and let us know..

Thanks,

-Kamal