Urgent! Make a field value 0 based on repeating ID.

[Mapuna]

I have a dataset in which a project_id and funding_company and amount columns exist. if same project id has two different funding_company, and if one of the company is "CompanyABC" then the amount column of "CompanyABC" must be made 0.  If only one funding_comapany exists for a project_id, then the amount should remain same. Backend script would be appreciated.How to achieve this in Qlik sense?

[zhaofeng]

Try this

DataSource:
Load * Inline [
ProjectID,FundingCompany,Amount
101,xyz,20000
102,CompanyABC,80000
102,CompanyQWE,80000
103,CompanyABC,4000
];



CompanyAmount:
Load Distinct
    ProjectID,
    count(distinct FundingCompany) as CompanyAmount
Resident DataSource
group By
	ProjectID
;


NoConcatenate

DataSource_Temp2:
Load
    *
Resident DataSource;

left join(DataSource_Temp2)

Load
    ProjectID,
    0 as multiplier
Resident CompanyAmount
where CompanyAmount = 2
;

NoConcatenate

DataSource_Temp3:
Load
    ProjectID,
    FundingCompany,
    Amount,
    if(FundingCompany='CompanyABC' and not isnull(multiplier),multiplier,1)	as multiplier
Resident DataSource_Temp2;


NoConcatenate

DataSource_Final:
Load
    ProjectID,
    FundingCompany,
    Amount*multiplier as Amount
Resident DataSource_Temp3;


Drop Tables DataSource,DataSource_Temp2,DataSource_Temp3,CompanyAmount;

[Mapuna · Accepted solution]

Thanks for the help. But i found a shorter solution and it worked. Hope this thread helps someone 🙂

Solution:

Left join(MAIN_TABLE)
Count:
LOAD
    Project_ID,
    count( distinct Funding_Company) as count
Resident MAIN_TABLE
Group By Project_ID;

Final_Table:
Load 
   *,
   if(count>1 and Funding_Company='CompanyABC',0,amount) as updated_amount;

Resident MAIN_TABLE;
Drop Table MAIN_TABLE;