Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
.
.
.
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
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.
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);
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)
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.
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
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)