topic (simple) Measuring sales ? in QlikView

(simple) Measuring sales ?

Mon, 11 Oct 2010 08:45:35 GMT

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 ?

(simple) Measuring sales ?

renjithpl — Mon, 11 Oct 2010 09:39:44 GMT

Hi,

If i understood your question correctly,

is it some thing lile below code..???

Load

Orderid,

If(Customer='', Customer2, Customer) as Customer,

If(Customer2='', Customer, Customer2) as Customer2,

Price

If not, please send me how you want your output to be displayed.

Regards

REn

(simple) Measuring sales ?

Mon, 11 Oct 2010 09:48:31 GMT

so, you need to show the total sales by the account manager against particular customer,

so for product table u should take the account manager field and that is as same as the name of the fields in the sales table..

example

sales table Product Table
actMgrName actMgrName

and then associate both table with this actMgrName field
and when user select the account manager name in the select box then

he/she can see the total sales(measure) for customer by account manager

(simple) Measuring sales ?

Mon, 11 Oct 2010 11:20:14 GMT

Ranjit, something like that, yes!

I hope my loading script helps a bit by explaining what I need.

LOAD KLT, "KLT_1",

FAC,

JAA,

PER,

OMS,

BEDRAG,

AFR,

REFKLA,

FAKMAKEN;

SQL SELECT KLT,

FAC,

JAA,

PER,

OMS,

BEDRAG,

"KLT_1",

AFR,

REFKLA,

FAKMAKEN

FROM IHFAC03;

Where KLT and "KLT_1" stands for Customer (id's).

I need to have a script that loads the "KLT_1" as KeyCustomer, where when "KLT_1" is empty it loads the value of KLT as KeyCustomer. Because I use another table that matches the value in KeyCustomer to the accountmanager that is responsible for the account.

So I want to merge KLT and "KLT_1" to 1 table, where "KLT_1" is leading, and when this field it empty it fills with the value from KLT.

Do you know what I mean ?

(simple) Measuring sales ?

renjithpl — Mon, 11 Oct 2010 11:46:06 GMT

Hi,

little modified my above code,

LOAD KLT,

If (KLT_1='', KLT, KLT_1) as Customer,

FAC,

JAA,

PER,

OMS,

BEDRAG,

AFR,

REFKLA,

FAKMAKEN;

And in the table you can select Customer and Orderid as dimension and Price as expression.

Please let me know if you have any issues in the above code,

Regards

Ren

(simple) Measuring sales ?

Mon, 11 Oct 2010 12:19:53 GMT

Oke, I used your loading script, But it still does not work.

I want to load the value from "KLT_1" as Customer where if "KLT_1" is empty it should use the value from KLT as KeyCustomer.

So using your loading script.. KeyCustomer is still blank, where there should be visible Z06. Thats because KLT has a value of Z06 and "KLT_1" does not have a value. Do you know what I mean ? Hope so : )

Something like:

LOAD KLT as Customer,

If (KLT_1='', KLT, KLT_1) as Customer,

FAC,

JAA,

PER,

OMS,

BEDRAG,

AFR,

REFKLA,

FAKMAKEN;

KeyCustomer KLT "KLT_1" Price

- Z06 - €999

(simple) Measuring sales ?

renjithpl — Mon, 11 Oct 2010 12:54:06 GMT

Hi friend,

i have attached an application, just go through it and get an idea.. If you still have any issues, just copy paste your code, will revert bact with the solution.

Thanks

Ren

(simple) Measuring sales ?

renjithpl — Mon, 11 Oct 2010 12:55:37 GMT

Hi,

I have attached an example, just go through it and get an idea, if you still have any issues, just copy paste your code which you are using in your application.

Regards

Ren

(simple) Measuring sales ?

Mon, 11 Oct 2010 13:30:35 GMT

Thanks for your help, but I still havent figuered it out. Let me begin again;

real values in our database:

KLT_1(customer) KLT(billing adress) price

ABC News ABS News Financial Department (Accountmanager value= looks at KLT_1) > Adam

empty ABS News Financial Department (Accountmanager value=looks at empty) > unknown acccuntmanager

I am looking for a load line that when KLT_1 is empty, KeyKLT gives ABS News Financial Department as output. So KeyKLT loads (KLT) because KLT_1 was empty.

So it's the same as joining ROW KLT with KLT_1, but this can only be done if KLT_1 is empty.

I hope I am making any sense 🙂

(simple) Measuring sales ?

renjithpl — Mon, 11 Oct 2010 13:48:53 GMT

just copy and paste here whatever code you typed, let me have a look at it and try to figure out where you made mistake. If you dont mind, you can attach your qvw file.

chill out

thanks..

(simple) Measuring sales ?

Mon, 11 Oct 2010 14:10:45 GMT

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 ?

(simple) Measuring sales ?

renjithpl — Mon, 11 Oct 2010 14:37:33 GMT

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

(simple) Measuring sales ?

Mon, 11 Oct 2010 14:44:50 GMT

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.

(simple) Measuring sales ?

renjithpl — Mon, 11 Oct 2010 19:31:45 GMT

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

(simple) Measuring sales ?

renjithpl — Mon, 11 Oct 2010 19:40:06 GMT

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

(simple) Measuring sales ?

Tue, 12 Oct 2010 11:04:53 GMT

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.

(simple) Measuring sales ?

renjithpl — Tue, 12 Oct 2010 12:49:27 GMT

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..

(simple) Measuring sales ?

renjithpl — Tue, 12 Oct 2010 12:53:35 GMT

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

(simple) Measuring sales ?

Thu, 14 Oct 2010 07:51:35 GMT

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 ?

(simple) Measuring sales ?

renjithpl — Thu, 14 Oct 2010 10:37:42 GMT

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