34 Replies Latest reply: Mar 6, 2015 8:09 AM by Thanh Phong Le

# query combining differents fields from 2 different columns

Asset cost
Product
1000A
25000B
8000C
4000A
15000A
12000B
500B
13000A

Hello Community, I would like to sum up every cost which refers to their own product

The query should be like this

if product=A, then sum up every asset costs referring to product A. If product= B, then sum up every asset costs referring to product B and if product C sum up every asset costs referring to product C

Qlikview should give out the result in this form

 Sum Asset Cost Product A Sum Asset Cost Product B Sum Asset Cost Product C 33000 37500 8000

in german

Anschaffungskosten
Produkt
1000A
25000B
8000C
4000A
15000A
12000B
500B
13000A

Hallo Community, ich würde gerne aus der obigen Tabelle, überall wo Produkt A, B oder C vorkommen die Anschaffungskosten miteinander addieren.

Die Abfrage wäre,

Wenn Produkt= A, dann addiere alle Anschaffungskosten bzgl. Produkt A, Wenn Produkt= B, dann addiere alle AK bzgl. B, wenn Produkt=C dann addiere alle AK bzgl C

Qlikview soll die Ergebnisse später in Form eines Tabellendiagramms ausgeben.

Summe Anschaffungskosten Produk A Summe Anschaffungskosten Produk B Summe Anschaffungskosten Produk C 33000 37500 8000
• ###### Re: query combining differents fields from 2 different columns

please find the sollution attached

• ###### Re: query combining differents fields from 2 different columns

Create a Straight table without any dimension and with three different expressions

=sum({<Product={A}>}[Asset Cost])

=sum({<Product={B}>}[Asset Cost])

=sum({<Product={C}>}[Asset Cost])

You will get the result

• ###### Re: query combining differents fields from 2 different columns

Thank you very much, I will check out if it works!

• ###### Re: query combining differents fields from 2 different columns

i was trying to apply your solution to my expressions. but it still shows me an error at the second brace by saying Invalid field: Inv.volumen.

I just copy my expressions which I want to combine together.

Investition -> sum([Selling price])

Monatsmiete -> Sum([Payment (Asset) p. m.])

Leasingfaktor -> =AVG(([Payment (Asset) p. m.]/[Selling price])*100)

Zuordnung ->

if(Leasingfaktor<1.5, 'Festkontraktierte Verlängerung',

if(Leasingfaktor=1.5 or Leasingfaktor<2.0, '60',

if(Leasingfaktor=2.0 or Leasingfaktor <2.4, '48','36')))

and the last expression which was your solution->

=sum({<Zuordnung={[Festkontraktierte Verlängerung]}>}Inv.volumen)

Error -> Invalid field: Inv.volumen. If i replace Inv.volumen with sum([selling price])

see here: =sum({<Zuordnung={[Festkontraktierte Verlängerung]}>}sum([selling price]))

it still tells me that there is an error at the second brace -> error in expression:

Thank you very much!

• ###### Re: query combining differents fields from 2 different columns

Can you provide a QVW so we can test it ?

• ###### Re: query combining differents fields from 2 different columns

yes, of course but how can i upload a qvw data here?

• ###### Re: query combining differents fields from 2 different columns

When you create a message, in the upper right part you will see advanced editor.

Now you will see the attachment option below right.

• ###### Re: query combining differents fields from 2 different columns

i m sorry i mean Inv.volumen -> sum([Selling price]) not Investition

I think the problem is, that Inv.volumen , Leasingfaktor, Zuordnung are not basic columns from my Excel Database.

• ###### Re: query combining differents fields from 2 different columns

Hey Thanh:

I will have a look at query and get back to you shortly. I am stuck with some work now. Sorry for inconvenience.

• ###### Re: query combining differents fields from 2 different columns

Could you please send a part of your data or the app, so that I could resolve the issue finally.

• ###### Re: query combining differents fields from 2 different columns

if u could do this and give me some explanaton with it, it would be really amazing!

• ###### Re: query combining differents fields from 2 different columns

Hi, I´m a little bit unsure of what you are trying to achieve.

I´ve fixed your Set Analysis expression, but as QlikRajan said, Set Analyisis can't skip dimensional filtering inside a chart. To do this, you need to use TOTAL .

Attached you will find an example with expression Inv.zuordnung - TOTAL . In this case, the expression  SUM(Selling Price) is being filtered only by Category dimension inside the chart

• ###### Re: query combining differents fields from 2 different columns

i actually want to create 4 barcharts.

The first barchart should show me the sum for only "Festkontraktierte Verlängerung"

The second barchart the sum for only "36 Monate"

The third barchart the sum for only "48 Monate"

and the fourth for the sum of only "60 Monate"

• ###### Re: query combining differents fields from 2 different columns

I will work on it and get back to you shortly.

• ###### Re: query combining differents fields from 2 different columns

Hi Thanh:

I have created 4 different charts as per requirements. I created some of the fields in script and then with the help of set analysis and conditional dimensions created these 4 charts. Please find attached.

Let me know if this is what you were looking for.

• ###### Re: query combining differents fields from 2 different columns

Unfortunetly i can't load it into my script. maybe i'm just too stupid for it. but this is how i add ur solution into my script

but it shows me 3 errors....

1. Error

2. Error

3. Error

My origin script basically looks like this

Main script

Basisdaten script

could u may help me adding ur solution to my basic script?

• ###### Re: query combining differents fields from 2 different columns

could u help me out there?

• ###### Re: query combining differents fields from 2 different columns

I will get back to you shortly. I will work on this after my office hours.

• ###### Re: query combining differents fields from 2 different columns

have you found a solution for my errors yet?

• ###### Re: query combining differents fields from 2 different columns

Hi there,

You are loading the wrong table in the script above. In your second script string, you load from A-Import_LPM, and then you enrich with AVG_PV and Zins. In the first script string you show, you load your main table again from A-Import_LPM. Zins is not in there, and so this is the first error message you get. The other follow out of the first.

Solution is to save the result of your joins in a file, and then use this file to load your main data from. This way, zins will be there, and the script should work.

Regards,

Johan

• ###### Re: query combining differents fields from 2 different columns

could you show me, how my new file should look like? it is completely new for me, working/manipulating with the script.

• ###### Re: query combining differents fields from 2 different columns

Could you upload your Qvw (that works easier than rescripting everything)

• ###### Re: query combining differents fields from 2 different columns

there is already one attached

• ###### Re: query combining differents fields from 2 different columns

Please have a look at this one. Hope it will not give you the errors anymore if you can complete the sources correctly.

• ###### Re: query combining differents fields from 2 different columns

it is working! thank you very much

• ###### Re: query combining differents fields from 2 different columns

Hey..I was travelling  last week for work. I could not have a chance to get back to qlik community. I have seen someone has already helped you on this. good luck

• ###### Re: query combining differents fields from 2 different columns

its ok, u helped me a lot and thank you very much for your help! but could you answer on my private message please ?

• ###### Re: query combining differents fields from 2 different columns

I have not seen your msg, cud you send again

• ###### Re: query combining differents fields from 2 different columns

i actually have still one problem left. everytime if i want to load a new excel database which i named like the the excel database before and which got the same fields (i added some more fields but it should not cause any problems with the loading) by clicking on the button "load script" it always shows me an error that it couldnt find some fields. so i always have to load the script manually by deleting the database in the script and readd it by selecting the database though giving the path. it s quite annoying to do it everytime cos i have a lot of database with different values, but the built of the database is still the same. usually it sould work on by clicking on the "script loading button" but it does not. anyone knows this problem?

could the problem be, because i removed

Directory;

....

?

• ###### Re: query combining differents fields from 2 different columns

In order to load a new file (enriched I guess), you need to place this file in the exact directory as the old one, with the same name. Then, if the same variables are in (an extra capital in the name can be a difference and cause an error to occur), the reload should be ok.

• ###### Re: query combining differents fields from 2 different columns

ok thank you