(simple) Measuring sales ?

[#1]

I am builing a table that needs to show the amount of sales of an accountmanager.

In the customer table there is a relation between customer and accountmanager.

Below is an example of my order table;

OrdeId | Customer | Customer_2 | PRICE |
#1 | (H01) Heineken | empty | 100€ |
#2 | empty | (H01) Heineken | 200€ |

Now I can check the Customer database which accountmanager is matched with Customer and output this in a field. The problem is that sometimes orders are placed when Customer is empty but Customer_2 is used.

I used a Load Customer as KEYCUSTOMER and looked it up in a table, which worked. But I cannot set Load Customer_2 as KEYCUSTOMER also because it gives me back errors.

Any ideas ?

[#2]

Where do i need to use that code in, in the loading script or in my graph as a dimension ?

Check the attachment to understand how I measure sales by Accountmanager.

IFHAC3 - is our financial record

In IHFAC3 there are 2 fields that hold the customer ID, thats KLT & KLT_1.

in table KLANTEN there is the record KLT and TBNVT.

in the TBNVT table there are the names of the Accountmanager., So IHFAC is linked (KLT) to Klanten table and Klanten table looks the name of the accountmanager up. But the problem is that I cannot link "KLT_!" to the table Klanten, because in table Klanten there is only KLT and not a KLT_1 field. So, I am missing these values.

[renjithpl]

ok, if that is the case,

you can give the below code in your dimension of your straight table.

=If(Accountmanager=KLT_1, Accountmanager, if(isnull(KLT_1), KLT, if(Accountmanager=KLT, AccountManager)) )

Regards

Ren

[#4]

Besides putting that in my straight table, Can I also put this in the loading script, where in the load the fields KLT and KLT_1 are checked withg this condition ?

[renjithpl]

so you have Tabellen table right ?

so before Tabellen table

give join, and you code should look like,

Join Tabellen:

Load

TBNVT,

Accountmanager

from ......;

then after this,

Just type this code structure like below:

AcntMgr:

Load *,

If(Accountmanager=KLT_1, Accountmanager, if(isnull(KLT_1), KLT, if(Accountmanager=KLT, AccountManager)) ) as Accountmanager_1

Resident Klanten;

Drop Table Klanten;

after executing, you will have a Accountmanager_1 field which satisfies your question above. the you need not to give in the dimension. You can select Accountmanager_1 in your dimension.

hope this will help you.

If you still have any issues, please send me rough data with all your tables in an excel format.

i will work out and send you a qvw file, then you can actully use your original data in that.

Best regards

Ren.

[#6]

Hi Renjit,

I put some files online for you to use... the main goal is to have TBNVT (Accountmanager) filled for every KLT_1 or KLT when KLT_1 is not filled/blank.