Compare two fields

[qlikwiz123]

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

.

.

.

[Anil_Babu_Samineni]

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

[qlikwiz123]

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.

[Anil_Babu_Samineni]

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);

[qlikwiz123]

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)

[Anil_Babu_Samineni]

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.

[avinashelite]

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

[qlikwiz123]

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)