Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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 ?
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
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.
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
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
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.
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..
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
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 ?
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