Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Please attach the sample with complete description
sample attached already..just need to know how to make the tables with 100% subset ratio
any help
could you please post your script , I don't have the QV license
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';
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
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?
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);
I have corrected your script.. pls reload and let us know..
Thanks,
-Kamal