Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

thanhphongle
Contributor

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
33000375008000

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 BSumme Anschaffungskosten Produk C
33000375008000
1 Solution

Accepted Solutions
puttemans
Valued Contributor

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.

32 Replies
qlikrajan
Contributor III

Re: query combining differents fields from 2 different columns

please find the sollution attached

qlikrajan
Contributor III

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

thanhphongle
Contributor

Re: query combining differents fields from 2 different columns

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

thanhphongle
Contributor

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:support.jpg

could you please help me here again.

Thank you very much!

thanhphongle
Contributor

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.

qlikrajan
Contributor III

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.

Not applicable

Re: query combining differents fields from 2 different columns

Can you provide a QVW so we can test it ?

thanhphongle
Contributor

Re: query combining differents fields from 2 different columns

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

thanhphongle
Contributor

Re: query combining differents fields from 2 different columns

here s a link where u can download it

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

Community Browser