topic Comparison Of Values in QlikView

Comparison Of Values

Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

I have a requirement where in TAble A i have values 1,2,3,4, and i have Table B with values 5,6,7,8

Now i need the maximum difference of the values when i do difference values of Table B - Table A. How do i attain that using Qlikview .

And also can any one guide me to set analysis if we can use here and any referencing link

Re: Comparison Of Values

swuehl — Wed, 03 Aug 2016 21:52:38 GMT

Not sure how these tables are related and based on which relation you want to compare your values.

Maybe just join both tables by record number:

LOAD Recno() as RecID,

ValueA

FROM TableA;

JOIN

LOAD Recno() as RecID,

ValueB

FROM TableB;

Then you can calculate the max difference in a text box like

=Max( fabs( ValueA - ValueB) )

Re: Comparison Of Values

ramasaisaksoft — Thu, 04 Aug 2016 05:29:14 GMT

Hi Bharat,

we are unable to understand your query.

as per my understanding i think u need to subtract values from

=aggr((field b-field a),some join key ex:-product no)

please correct me with sample data and Expected out put ,if understand wrongly.

Re: Comparison Of Values

avinashelite — Thu, 04 Aug 2016 05:36:25 GMT

Hi Rama,

Please share the sample data and the excepted output , so that it will helps to understand better

Re: Comparison Of Values

sdmech81 — Thu, 04 Aug 2016 06:22:14 GMT

Hi,

qsn is bit confusing.You want to show that max difference in text box ??

If that is the case then fetch min(table 1) and Max(table 2) and then deduct the 2.

if in table please elaborate it..

--sachin

Re: Comparison Of Values

Thu, 04 Aug 2016 15:53:50 GMT

Hi Thanks for that , but let me rephrase my question in clear way . We have Column 2 common in both tables .

FILE_T ( Table 1) INTERF_T( Table 2)

SLA (Column1) INTF_ID ( Column2 ) Receptpion value ( Column 1) INT_ID (Column2)

15 1 10 1

25 2 20 2

35 3 30 3

45 4 39 4

I would like to get that Reception value where SLA-Reception value has the max value ,

For the above given Ex: max (SLA-Reception value ) is 45-39 =6. So Finally we need the Reception value associated with the max difference. In this case its is 39 .

Re: Comparison Of Values

Thu, 04 Aug 2016 15:53:58 GMT

Hi Thanks for that , but let me rephrase my question in clear way . We have Column 2 common in both tables .

FILE_T ( Table 1) INTERF_T( Table 2)

SLA (Column1) INTF_ID ( Column2 ) Receptpion value ( Column 1) INT_ID (Column2)

15 1 10 1

25 2 20 2

35 3 30 3

45 4 39 4

I would like to get that Reception value where SLA-Reception value has the max value ,

For the above given Ex: max (SLA-Reception value ) is 45-39 =6. So Finally we need the Reception value associated with the max difference. In this case its is 39 .

Re: Comparison Of Values

Thu, 04 Aug 2016 15:54:19 GMT

Hi Thanks for that , but let me rephrase my question in clear way . We have Column 2 common in both tables .

FILE_T ( Table 1) INTERF_T( Table 2)

SLA (Column1) INTF_ID ( Column2 ) Receptpion value ( Column 1) INT_ID (Column2)

15 1 10 1

25 2 20 2

35 3 30 3

45 4 39 4

I would like to get that Reception value where SLA-Reception value has the max value ,

For the above given Ex: max (SLA-Reception value ) is 45-39 =6. So Finally we need the Reception value associated with the max difference. In this case its is 39 .

Re: Comparison Of Values

Thu, 04 Aug 2016 15:54:34 GMT

Hi Thanks for that , but let me rephrase my question in clear way . We have Column 2 common in both tables .

FILE_T ( Table 1) INTERF_T( Table 2)

SLA (Column1) INTF_ID ( Column2 ) Receptpion value ( Column 1) INT_ID (Column2)

15 1 10 1

25 2 20 2

35 3 30 3

45 4 39 4

I would like to get that Reception value where SLA-Reception value has the max value ,

For the above given Ex: max (SLA-Reception value ) is 45-39 =6. So Finally we need the Reception value associated with the max difference. In this case its is 39 .

Re: Comparison Of Values

Thu, 04 Aug 2016 16:00:41 GMT

Having said that i would to mention one more thing ,that our query is already complex with Joins . I dont want to write a join , rather i would like the comparison to be handled in Qlikview.

Re: Comparison Of Values

swuehl — Thu, 04 Aug 2016 16:31:23 GMT

So these tables are not linked in your data model at all? That's quite unusual.

If you can't change this, you can try something like

=Max([SLA]) - Min({<INT_ID = {'$(=Only({<[SLA] = {"$(=Max([SLA]))"}>} INTF_ID))'}>} [Receptpion value])

Re: Comparison Of Values

Thu, 04 Aug 2016 18:25:22 GMT

Hi Swehul,

But the $ thing is going to do selction right , We want to do for all values.

Re: Comparison Of Values

swuehl — Thu, 04 Aug 2016 19:01:26 GMT

Then please start with a more complete description of your setting and requirements, best by uploading a small sample file or modification of the sample I've provided.

Add a description / Excel with the expected results.

Re: Comparison Of Values

sdmech81 — Fri, 05 Aug 2016 07:22:24 GMT

Hi,

1)Hope, Stefans set should give u correct value.In case u want to perform aggregation on entire set then replace $ with 1 and check.

2)But when u already have 2 tables with common key, why cant u bring them in one table by doing applymap and fetch the result as we denormalize to create fact in qlikview.It should not be causing perf problem.share table viewer.

-sachin