topic Re: Compare two fields in QlikView

Compare two fields

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

I have to compare two fields to check if it is repeating in the second field. The field values are not numerical values.

In January ,ApplicationNames has applications for the month of January and for February ,ApplicationNames has applications for that month and so on.... I need to check if the applications from January exist in the applications field of February and identify the missing ones. Similarly, I need to do the same for February-March and so on. How do I achieve this?

Below is my Table Structure for some idea:

January:

ApplicationNames

Month

Concatenate

February:

ApplicationNames

Month

Re: Compare two fields

Anil_Babu_Samineni — Thu, 28 Dec 2017 17:30:35 GMT

Are you going to check Row level? Can you explain with 5 / 5 rows from 2 tables and expected result?

May be helps

Exists - script function ‒ QlikView\

Understanding EXISTS() function - Qlikview vs SQL/RDBMS

Re: Compare two fields

qlikwiz123 — Thu, 28 Dec 2017 17:39:23 GMT

To answer your question about Row Level, Yes and No.

Sample Data:

January:

Month

ApplicationName

February:

Month

ApplicationName

ApplicationName (January) Values

App1

App2

App3

App4

App5

ApplicationName (February) Values

App1

App3

App4

Here, App2 and App5 are not present in February ApplicationName field. So I need to identify the missing values (or) the present ApplicationNames that are present in both the months and show them.

The row level cannot be strictly implemented as the ApplicatioNames can be up and down in the Fields.

Re: Compare two fields

Anil_Babu_Samineni — Fri, 29 Dec 2017 01:10:41 GMT

I already provide link, Can you try this way?

Feb:

Load *, 'Feb' as Flag Inline [

Application, Month

App1, 2

App3, 2

App4, 2

];

Jan:

Load *, 'Jan' as Flag Inline [

Application, Month

App1, 1

App2, 1

App3, 1

App4, 1

App5, 1

] Where Exists(Application);

Re: Compare two fields

qlikwiz123 — Fri, 29 Dec 2017 15:05:51 GMT

You are creating an Inline table here. I cannot do that as I have the data present in the fields, already. Even if I use Resident load to pull the filed from one table, I need to pull the second field from Table 2 to compare, which isn't possible in Resident load.

Just to explain my problem statement more clearly,

Here are the Tables and Fields present in my Data Model:

Tables:

January

February

December

Each Table has the following columns:

ApplicationName

Month

Now, I need to compare Application Names present in current month and previous month and show the Application Names that are present in the current month and the missing ones too. (For instance, compare Application Names between January Table's Application Names and February Table's Application Names and show the ones present in the February)

Re: Compare two fields

Anil_Babu_Samineni — Sat, 30 Dec 2017 15:53:45 GMT

You need Applymap concept in qlikview first to make multi tables into one. Then, You could multi using Exists() function to last table to get same result.

Re: Compare two fields

avinashelite — Mon, 01 Jan 2018 09:31:20 GMT

Try like this , first load all the month data into one table

JAN:

LOAD

ApplicationName as Master_ApplicationName

from

JAN

concatenate

LOAD

ApplicationName as Master_ApplicationName

from

FEB

etc for all the nonths

then in each month load ..add this flag condition that will give you the results

E>G

JAN:

LOAD

ApplicationName,

if(EXISTS(Master_ApplicationName,ApplicationName ) ,'YES','NO') as Flag

apply the same for all the month data ..hope you got the answer

Re: Compare two fields

qlikwiz123 — Tue, 02 Jan 2018 16:59:13 GMT

To make this whole thing simpler, here is the new problem statement.

I have the below fields

Applications

Month

Year

Users

I need to show the difference between the users in Jan and each month. i.e; For the month of Feb, I need to show the difference between no. of users in Jan and Feb, for Mar, show difference in no.of users in Jan and Mar and so on...

Basically, show users added/deleted in each month. Key is to populate the Difference column

Applications Month Users Difference

ABC Jan 500 0

XYZ Feb 400 100

TNT March 450 50

AAA April 300 200

....

For every month, it should be (No. of Users in Jan) - (No. of users in that Particular Month)