topic Re: Mapping? Concatenate? Mess-up??!! in QlikView

Mapping? Concatenate? Mess-up??!!

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

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

Re: Mapping? Concatenate? Mess-up??!!

miguelbraga — Tue, 13 Sep 2016 13:32:23 GMT

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:

Try to add a Flag in all the 3 tables like this:
- 'Business_Owner_AC3' as AC3 -> in Business Owner table
- 'Debt_AC3' as AC3 -> in Debt table
Then concatenate all 3 tables that have almost identical field to get rid of the sync tables you probably are generating.
Tell me if this helps...

Hope this helps

Regards,

Re: Mapping? Concatenate? Mess-up??!!

effinty2112 — Tue, 13 Sep 2016 14:13:35 GMT

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

Re: Mapping? Concatenate? Mess-up??!!

rupamjyotidas — Tue, 13 Sep 2016 14:16:23 GMT

Why not Flag each table with a Flag and Concatenate. That would solve many key problem.

Thanks

Rupam