topic Re: query combining differents fields from 2 different columns in Qlik Learning Discussions

query combining differents fields from 2 different columns

thanhphongle — Thu, 19 Feb 2015 14:36:49 GMT

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

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
1000	A
25000	B
8000	C
4000	A
15000	A
12000	B
500	B
13000	A

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

Anonymous — Thu, 19 Feb 2015 14:50:06 GMT

please find the sollution attached

Re: query combining differents fields from 2 different columns

Anonymous — Thu, 19 Feb 2015 14:53:50 GMT

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

thanhphongle — Mon, 23 Feb 2015 08:57:49 GMT

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

Re: query combining differents fields from 2 different columns

thanhphongle — Mon, 23 Feb 2015 09:56:54 GMT

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:

could you please help me here again.

Thank you very much!

Re: query combining differents fields from 2 different columns

thanhphongle — Mon, 23 Feb 2015 11:50:48 GMT

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

Anonymous — Mon, 23 Feb 2015 15:07:07 GMT

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

Mon, 23 Feb 2015 15:21:32 GMT

Can you provide a QVW so we can test it ?

Re: query combining differents fields from 2 different columns

thanhphongle — Mon, 23 Feb 2015 15:47:05 GMT

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

Re: query combining differents fields from 2 different columns

thanhphongle — Mon, 23 Feb 2015 16:06:38 GMT

here s a link where u can download it

http://s000.tinyupload.com/?file_id=25949478933475615478

Re: query combining differents fields from 2 different columns

Mon, 23 Feb 2015 16:17:38 GMT

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

Anonymous — Mon, 23 Feb 2015 16:24:56 GMT

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

thanhphongle — Tue, 24 Feb 2015 08:48:20 GMT

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

Tue, 24 Feb 2015 11:24:40 GMT

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

thanhphongle — Tue, 24 Feb 2015 13:27:43 GMT

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

Anonymous — Tue, 24 Feb 2015 14:48:09 GMT

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

Re: query combining differents fields from 2 different columns

Anonymous — Tue, 24 Feb 2015 21:04:42 GMT

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

thanhphongle — Wed, 25 Feb 2015 10:35:44 GMT

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

thanhphongle — Thu, 26 Feb 2015 12:51:46 GMT

could u help me out there?

Re: query combining differents fields from 2 different columns

Anonymous — Thu, 26 Feb 2015 14:37:17 GMT

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