topic Re: Subset Ratio help in QlikView

Subset Ratio help

gauthamchilled — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Subset Ratio help

avinashelite — Mon, 20 Jun 2016 05:37:57 GMT

Please attach the sample with complete description

Re: Subset Ratio help

gauthamchilled — Mon, 20 Jun 2016 05:41:10 GMT

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

Re: Subset Ratio help

gauthamchilled — Mon, 20 Jun 2016 05:58:18 GMT

any help

Re: Subset Ratio help

avinashelite — Mon, 20 Jun 2016 06:01:31 GMT

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

Re: Subset Ratio help

gauthamchilled — Mon, 20 Jun 2016 06:22:12 GMT

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

Re: Subset Ratio help

avinashelite — Mon, 20 Jun 2016 06:52:48 GMT

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

Re: Subset Ratio help

gauthamchilled — Mon, 20 Jun 2016 07:30:54 GMT

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?

Re: Subset Ratio help

avinashelite — Mon, 20 Jun 2016 07:40:53 GMT

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;

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

Re: Subset Ratio help

kamal_sanguri — Mon, 20 Jun 2016 08:20:59 GMT

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

Thanks,

-Kamal