topic Re: Set Expression to get the remark based on amount in QlikView

Set Expression to get the remark based on amount

Tue, 12 Apr 2016 06:38:50 GMT

Hi All,

I have below data set

ID	Remark	Amount
1	A	100
1	B	200
1	C	300
2	E	250
2	F	300

Now I want the Remark for each ID where Amount is between 200 and 300. Below is the required output

ID	Amount	Remark
1	200	B
2	250	E

Re: Set Expression to get the remark based on amount

Kushal_Chawda — Tue, 12 Apr 2016 06:42:54 GMT

Create the straight table

Dimension:

Remark

Expression:

=sum({<Remark ={"=Sum(Amount)>200 and Sum(Amount)<300"}>}Amount)

Re: Set Expression to get the remark based on amount

vikasmahajan — Tue, 12 Apr 2016 06:57:52 GMT

=sum({<Remark ={ 'B','E' }>}Amount)

Re: Set Expression to get the remark based on amount

Tue, 12 Apr 2016 07:16:09 GMT

I want the Remark based on amount where amount is between 200 and 300

Re: Set Expression to get the remark based on amount

kkkumar82 — Tue, 12 Apr 2016 07:19:55 GMT

As per kush this is what you will get

Re: Set Expression to get the remark based on amount

_armoco_ — Tue, 12 Apr 2016 07:38:18 GMT

Hi,

When you say between 200 and 300 does it include the upper and lower limit values as well?

In the sample output you provided, you've included the lower limit of the Amount value(200) however you didn't include the upper limit value(300)....

Anyway, using distinct in the back-end, Amount>=200 and Amount<300 in the front-end, I could come up with this:

Is this what you wanted?

Re: Set Expression to get the remark based on amount

Anonymous — Tue, 12 Apr 2016 07:44:40 GMT

may be like this?

Dimesnion: ID, Remark

Expression: sum(if(Amount>200 and Amount<300, Amount))

Re: Set Expression to get the remark based on amount

_armoco_ — Tue, 12 Apr 2016 07:51:52 GMT

Hi,

This also seems to work:

Thanks and Regards,

Arjun

Re: Set Expression to get the remark based on amount

sunny_talwar — Tue, 12 Apr 2016 08:13:58 GMT

When you say between 200 and 300 does it include the boundary values 200 and 300 or not? Why did you include B for ID 1 and exclude C for the same ID?

Similarly, you excluded F for ID 2

What would happen if you have something like this

ID, REMARK, AMOUNT

3, G, 300

3, H, 400

Would you now pick ID 3 and remark G or you won't?

Re: Set Expression to get the remark based on amount

Tue, 12 Apr 2016 08:48:47 GMT

No sunny, I wont pick that, I just want the ID or remark where Amount is in between 200 and 300.

Re: Set Expression to get the remark based on amount

sunny_talwar — Tue, 12 Apr 2016 08:50:30 GMT

I understand that it needs to be between 200 and 300. But is 200 considered to be between 200 and 300, but 300 is not considered to be between 200 and 300?

Re: Set Expression to get the remark based on amount

Tue, 12 Apr 2016 08:52:59 GMT

200 and 300 will not be considered.

Re: Set Expression to get the remark based on amount

sunny_talwar — Tue, 12 Apr 2016 08:55:12 GMT

Then how can this be an output? I mean you don't have to respond back if you think you have found your solution. But I was just confused of how you decide the logic. Just wanted to offer my 2 cents

Re: Set Expression to get the remark based on amount

Kushal_Chawda — Tue, 12 Apr 2016 09:00:01 GMT

I am too confused

Re: Set Expression to get the remark based on amount

sunny_talwar — Tue, 12 Apr 2016 09:01:36 GMT

Glad I am not the only one

Re: Set Expression to get the remark based on amount

Tue, 12 Apr 2016 09:07:51 GMT

Sunny, for my actual scenario I have another threshold which is like below

TD_Amount > 10000 and TD_Amount < 20000 which is working fine. May be I have not put the scenario very well.

Re: Set Expression to get the remark based on amount

sunny_talwar — Tue, 12 Apr 2016 09:08:55 GMT

Sounds good. It all make sense now