topic Re: Match Column Data and get Value in QlikView

Match Column Data and get Value

Mon, 30 Nov 2015 15:25:42 GMT

A	B
1	100
2	99
3	98
100	1
101	0

I have this table.

I want to set B value ,If my value equals A .

For example ;

C = 1 => 100

C=100 => 1

in my pivot table.

Have nice a day.

Re: Match Column Data and get Value

sunny_talwar — Mon, 30 Nov 2015 17:26:09 GMT

Not sure where is C coming in from? Can you please elaborate?

Re: Match Column Data and get Value

Tue, 01 Dec 2015 08:09:21 GMT

For example Sum(Quantity) for C

I want to insert one column in table if Quantity 30 (in A) to 100(in B)

if greater than 120 , set value 0

Thanks your interest.

@Sunny T

Re: Match Column Data and get Value

Anonymous — Tue, 01 Dec 2015 08:16:03 GMT

You want to have 3rd column C as a expression??

Re: Match Column Data and get Value

Tue, 01 Dec 2015 08:19:47 GMT

Yes I want to have 3rd column .

Thanks.

Re: Match Column Data and get Value

jyothish8807 — Tue, 01 Dec 2015 08:27:25 GMT

Hi Murat,

Try like this in script:

Load A,

if(A='1' and C='1','100',if(A='2' and C='2',99...... ) as B

from

Hope it helps.

This is what i understand from your requirement.

Please elaborate a bit more about your requirement for better solutions.

Regards

Re: Match Column Data and get Value

Tue, 01 Dec 2015 08:44:09 GMT

Thank you Jyothish.

I can use your script but I want to find easy way for this.

I try to explain more understandable .

C is my expression column.

A and B insert data(in excel).

If C equals A , I want to get B value .

so If C =1 and look A and find 1 value after that get value in B (100) for A(1).

Re: Match Column Data and get Value

Mark_Little — Tue, 01 Dec 2015 08:59:54 GMT

HI,

Can you elaborate a little?

Not sure what you are trying to get to. It will be some form of if statement, but not knowing the full ins or outs it is hard to say exactly what to do and if it would be best to approach in the script.

Mark

Re: Match Column Data and get Value

jyothish8807 — Tue, 01 Dec 2015 09:15:40 GMT

Hi Murat,

Over here when you say 'C', is it sum(Quantity) ?

Its still confusing.

Regards

Re: Match Column Data and get Value

Tue, 01 Dec 2015 09:20:42 GMT

Hi Jyothish,

Yes C is sum(Quantity)

Thanks.

Re: Match Column Data and get Value

jyothish8807 — Tue, 01 Dec 2015 09:50:26 GMT

Hi Murat,

Try doing mapping load.

tab1:

mapping load

resident.

In your table:

load *,

applymap('tab1',sum(Quantity)) as B

resident <>.

Then use this field.

Regards

Re: Match Column Data and get Value

sunny_talwar — Tue, 01 Dec 2015 11:12:15 GMT

This?

Expression: =If(Sum(Quantity) > 120, 0, Only(Aggr(If(A = Sum(Quantity), B), CustomerID, A, B)))

Update: Although the solution is working, I don't recommend this as this Aggr() function can take a lot of your system resources for a bigger data set. The same can be done in script which might take some time to run in the script, but will not affect your front end.

Re: Match Column Data and get Value

Tue, 01 Dec 2015 11:30:14 GMT

Thank you so much Sunny

dimesion : Grup

first exp: Vade => Sum({<Rapor={"Satış"},VadeHesapla={"1"}>}VadeEurPuan) / Sum({<Rapor={"Satış"},VadeHesapla={"1"}>}Eur)

second exp: If("Vade" > 120, 0, Only(Aggr(If(A = "Vade", B), Grup, A, B)))

I try this but not work only works "Vade" > 120, 0,

Why not workıng this part Only(Aggr(If(A = "Vade", B), Grup, A, B)))

Re: Match Column Data and get Value

Tue, 01 Dec 2015 11:31:07 GMT

I want to set in expression.

Thank you so much for your interest

Re: Match Column Data and get Value

sunny_talwar — Tue, 01 Dec 2015 13:23:15 GMT

I don't think expression name will work here, you will have to use the whole expression within Aggr:

If("Vade" > 120, 0, Only(Aggr(If(A = Sum({<Rapor={"Satış"},VadeHesapla={"1"}>}VadeEurPuan) / Sum({<Rapor={"Satış"},VadeHesapla={"1"}>}Eur), B), Grup, A, B)))

Re: Match Column Data and get Value

Tue, 01 Dec 2015 13:32:09 GMT

Thank you Sunny. My problem is extension of number. I used ceil and fixed.

Re: Match Column Data and get Value

sunny_talwar — Tue, 01 Dec 2015 17:58:11 GMT

Not sure I understand, what do you mean by extension?

Re: Match Column Data and get Value

Tue, 01 Dec 2015 18:45:01 GMT

I am sorry , I mean my number was float so I changed integer and it works.

Re: Match Column Data and get Value

sunny_talwar — Tue, 01 Dec 2015 18:53:40 GMT

So all is working now?

Re: Match Column Data and get Value

Tue, 01 Dec 2015 18:56:16 GMT

Yes, All works now.