(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]

Oke, see my Qlikview template (temp).

I would like to have a column that shows all the values from KLT_1 and KLT.

So in this case, I am looking for a load script that loads both KLT_1 and KLT in 1 column. So in this case I am looking for a colomn that loads

all the L00 + H01 + W01 into 1 column, lets call it KeyCustomer. Do you see my problem ?

[renjithpl]

hey,

just use the below code and check,

LOAD ["KLT_1"],

KLT,

If(["KLT_1"]='', KLT, ["KLT_1"]) as KeyCustomer

TOTBEG,

AFR,

BEDRAG,

FAC,

FAKMAKEN,

JAA,

OMS,

PER,

REFKLA,

DEBCREVOO,

Accountmanager

FROM

(ooxml, embedded labels, table is Sheet1);

please instead of selecting KLT or KLT_1,

Just select KeyCustomer in the list box and see.

The KeyCustomer has all the values of KLT and KLT_1.

REgards

ren

[#4]

Ranjit, I am not looking for a selection box where all the values our shown...thats not my question.

I am looking for a column where KLT_1 and KLT are combined. I need to have the values of KLT_1 and KLT in 1 colomn.

e.g. LOAD "KLT_1" as KeyCustomer, KLT as KeyCustomer. But this (ofcourse) does not work. I need to join the values to one column.

[renjithpl]

Please send me your excel sheet with data.

Its not a selection box, you can use the KeyCustomer as a dimension too.

Ok. Follow this steps.

1. just copy my code in the load, then

2. execute.

3. right click and go to new sheet object -> Chart->select Straight table

4. in Dimension give KeyCustomer

4. In Expression give Price.

5. give apply and ok.

6. you will get a table without any empty fields.

once you execute the data, forget about KLT and KLT_1.

Concentrate on KeyCustomer.

If you still have issue, please send me your excel sheet.

i will do it and send it back to you.

Regards

Ranjit

[renjithpl]

Its not a selection box, i told you to just check using a list box,

If you want KeyCustomer as a column, only in a pivot or a straight table you can bring KeyCustomer as Column.

Thats why i explained you, how to bring KeyCustomer in a Pivot table or a straight table.

When you bring KeyCustomer in a listbox its a list box, when u bring KeyCustomer in a Table, it acts as a column.

In Qlikview, even the colun can be drill downed. so no matter if your field is in a list box or in a pie chart or in a tables.

Please send me the excel file, i will finish y0our doubts you have.

Thanks

Ren

[#7]

I still don't completely understand.. I have attached a screenshot to make things a bit clearer.

I used a pivot table with Keycustomer as dimensions, but I am still missing sales amount. Check the screenshot.

[renjithpl]

Hey ,

Try this below code, it would definetly work..

Customer:

LOAD KLT,

KLT_1,

If( isnull(KLT_1), KLT, KLT_1) as KeyCustomer,

Amount,

AccountManager

FROM

Customer.xlsx

(ooxml, embedded labels, table is Ark1);

I am attaching an application tooo..

[renjithpl]

hi

I am sending the excel sheet too, for your clarification.

in some cases, Isnull function works.

You can change your coding accordingly,

Basically

If(Isnull(["KLT_1"], KLT, ["KLT_1"]) as KeyCustomer.

Thanks

Ren

[#10]

Dear Renjit,

My complete problem we talked about was, that i cannot match the correct Accountmanager to an order line. That is because my lookup table for accountmanager looks at "KLT" and not all my sales revenue is 100% linked to KLT. It is 90% KLT_1 and 10% KLT. This has something to do with our administration department. I thought that it would be easy to make a new column that where if KLT_1 was empty, it would put the value in for KLT. So then if I was to lookup the Accountmanager I could use KeyCustomer as reference. Despite our efforts this does not seem to work. Maybe we can try another approach ???

The problem is Accountmanager should be matched with KLT_1, and if this cell is empty, it needs to lookup the name of the accountmanager with KLT.

Perhaps there is another approach.. I don't really need all the values of KLT_1 and if KLT=null, do KLT in KeyCustomer. I am having a table (TBNVT) that contains the name of an accountmanager. I need to match this with KLT or KLT_1. Do you know a way?

Klanten:

LOAD KLT as KeyCustomer,

NAAM as Klantnaam,

TBNVT, TBNEK;

SQL SELECT KLT,

NAAM, TBNVT, TBNEK

FROM ISBKLT;

Tabellen:

LOAD TBNVT,

"TBNVT_oms" as Accountmanager;

SQL SELECT *

FROM TBNVT;

The problem is, that in this situation Qlikview only looks at KLT .. and it needs to match the Accountmanager with KLT_1 or is KLT_1 is null it needs to match KLT. Do you know what I mean ?

[renjithpl]

hi chris,

i think, if i understood your scenario correctly,

you can try something like this

If(AccountManager=KLT_1, AccountManager, if(AccountManager=KLT, KLT)) as AccountManager

If you still have some problem,

Send me a dummy data in excel,

Regards