topic Re: Expression in QlikView

Expression

prabunakil — Wed, 25 Nov 2020 16:16:04 GMT

Hi all, please assist to share the expression.

My criteria is as per below:

1. [Bill Type] = {'ZLF2'}, [KSCHL]={'Z1PR'}

2. [Billing Item] = [Billing Item2]

3. Sum [KONV_KWERT]

Re: Expression

prma7799 — Tue, 19 Jun 2018 05:11:31 GMT

Try this

Sum( {<[Billing Item] = {'[Billing Item2]'} , [Bill Type] = {'ZLF2'} ,[KSCHL]={'Z1PR'} >} [KONV_KWERT])

Re: Expression

qlikviewwizard — Tue, 19 Jun 2018 05:42:21 GMT

Hi,

Attach some sample data.

Re: Expression

prabunakil — Tue, 19 Jun 2018 05:56:14 GMT

this is the database. i want to link that two field together.

Re: Expression

prabunakil — Tue, 19 Jun 2018 05:56:31 GMT

answer return error. no matching.

Re: Expression

prma7799 — Tue, 19 Jun 2018 05:59:28 GMT

what error you are facing ?

Re: Expression

prabunakil — Tue, 19 Jun 2018 07:11:21 GMT

it show zero.

Re: Expression

prabunakil — Tue, 19 Jun 2018 07:12:13 GMT

i want to compare this two field and match it.

Re: Expression

prabunakil — Tue, 19 Jun 2018 09:41:10 GMT

Hi any solution?

Re: Expression

prma7799 — Tue, 19 Jun 2018 11:57:42 GMT

Please share QVW file

Re: Expression

jyothish8807 — Tue, 19 Jun 2018 12:01:25 GMT

Try this:

Sum( {<[Billing Item] = p([Billing Item2]) , [Bill Type] = {'ZLF2'} ,[KSCHL]={'Z1PR'} >} [KONV_KWERT])

Br,

Re: Expression

jyothish8807 — Tue, 19 Jun 2018 12:22:01 GMT

else you can try this also,

sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLF2')=1 and match([KSCHL],'Z1PR')=1, [KONV_KWERT]))

Re: Expression

PrashantSangle — Tue, 19 Jun 2018 12:34:22 GMT

if([Billing Item]=[Billing Item2],sum({<[Bill Type]={"ZLF2"},[KSCHL]={'Z1PR'}>} [KONV_KWERT]))

Regards

Re: Expression

jonathandienst — Tue, 19 Jun 2018 12:56:31 GMT

You can't do a row by row comparison directly using set analysis. The set filter is applied before the chart is built and not row by row.

You can use a Sum(if()) construct:

Sum({<[Bill Type] = {'ZLF2'}, [KSCHL] = {'Z1PR'}>} If([Billing Item] = [Billing Item2], [KONV_KWERT]))

Or if you have a suitable ID field, you could try:

Sum({<[SomeIDField = {"$(=[Billing Item] = [Billing Item2])"}, [Bill Type] = {'ZLF2'}, [KSCHL] = {'Z1PR'}>} [KONV_KWERT])

Or you could derive a flag field in the load script to indicate when the two fields are alike. That may not be easy in your case because they are not in the same table.