topic Re: Urgent! Make a field value 0 based on repeating ID. in App Development

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

Mapuna — Wed, 13 Sep 2023 06:02:58 GMT

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?

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

zhaofeng — Wed, 13 Sep 2023 07:35:09 GMT

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;

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

Mapuna — Wed, 13 Sep 2023 09:41:49 GMT

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;