Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I think I'm thinking about things to much, and have muddled my brain!!
I have three tables, all hold common fields, but one, 'Customer Number' as the link. My tables are Debt, Dashboard and Business Owner. In each of these tables I have a field that is named differently in each but is essentially the same, AC3. The AC3 changes depending on the customer number, and who in the business it is assigned too.
Rule is, that the AC3 to be assigned to the 'Customer' comes first from the 'Debt' table, if it can't be assigned from there, then it goes to the 'Business Owner', and then 'Dashboard'.
This, in English is what I want to achieve, but after 24 hours, I'm still not there!
If Debt_AC3 is null, or '95', look up Business_Owner_AC3, but if Business_Owner_AC3 is null, or '95', look up Dashboard_AC3, for anything other than null or '95', in the Debt_AC3, use the Debt_AC3.
I have this so far:
if(Len(Debt_AC3)=0, Business_Owner_AC3,
if(Debt_AC3='95', Business_Owner_AC3, Debt_AC3))
How do I add the extra rule, that if Business_Owner_AC3 is null, or '95', look up the Dashboard_AC3?
I thought a mapping might work, but the AC3 comes from three different tables, and when I tried, everything went haywire!
I've tried nesting another if in there, but Qlik said it was garbage!
Any ideas?
Thanks
Di
Hey there,
Can you please share with us a screenshot of your tableview or a sample of your qvw with scramble data differente from the your true values or even a sample of your data from a Excel/CSV/TXT file?
If you can't share anything from above, please do the following:
Hope this helps
Regards,
MB
Hi Diane,
Try three mapping tables:
DebtMapping:
Mapping
Load
[Customer Number],
Debt_AC3
Resident Debt;
BusinessOwnerMapping:
Mapping
Load
[Customer Number],
Business_Owner_AC3
Resident Business_Owner;
DashboardMapping:
Mapping
Load
[Customer Number],
Dashboard_AC3
Resident Dashboard;
Now apply your mappings like this
Load
.
.
ApplyMap('DebtMapping',[Customer Number],
ApplyMap('BusinessOwnerMapping',[Customer Number],
ApplyMap('DashboardMapping',[Customer Number],'Missing'))) as AC3,
.
.
Resident ...;
Good luck
Andrew
Why not Flag each table with a Flag and Concatenate. That would solve many key problem.
Thanks
Rupam