Solved: Displaying records only for satisfied condition - Qlik Community

Anonymous · ‎2016-02-24

Hi,

I have got monthly report of employees in form of two tables.

Emp-tableJan and Emp-tableFeb with same field names.

There are 4 fields namely,

Emp ID, Emp name, Grade Level, Designation.

For eg:

Emp-tableJan: (50 rows)

Emp ID Emp name Grade Level Designation

01 abc 01 desga

02 xyz 02 desgx

03 ..... 02 .......

Emp-tableFeb: (60 rows)

Emp ID Emp name Grade Level Designation

01 abc 02 desgb

02 xyz 03 desgx

03 ..... 02 .......

I want to display the data of "ONLY" those employees, whose Grade Level has been changed from first table to second table in a Pivot table with name and their two Grade Levels. i displayed, but i can see rest of those employees ID in tat. how do i ignore or remove it. Help me in doing tat.

Thanks.

Report Inappropriate Content · ‎2016-02-26

In my humble opinion, the merge that you've done as actually made this a slightly less pleasant data set to work with, but hey-ho...

Add the following (checking field names, and table names, because the names you give to your tables is invalid in your examples, so I've had to change them) to your loader script.

RoleHasChanged:

LOAD

EmpID,

IF ( COUNT ( DISTINCT [Grade Level] ) > 1, 1, 0 ) AS HasChanged

RESIDENT EmpTableAllMonth

GROUP BY [EmpID] ;

This is creating a table of flags (just as Anton Aleksandrov suggested a lot earlier). You can now use that to control the presence of an expression...

Create a pivot with three Dimensions, EmpID, Emp Name and Grade Level.

One Expression:

if ( HasChanged, [Month4] )

Only those IDs where a Grade change has occurred will be present as all expressions (the only expression) in the table will be NULL for all those where the HasChanged flag = 0.

View solution in original post

Anonymous · ‎2016-02-25

might be helpful for you?

Slowly Changing Dimensions

IntervalMatch and Slowly Changing Dimensions

asgardd2 · ‎2016-02-25

I think , you can pre-calculated flag in the script for thoose employees, who changed grade level .

Please, attach a sample.

Report Inappropriate Content · ‎2016-02-25

It's a little confusing as you've two separate tables here with identical column names, so my plan would be to merge these into a single table with multiple rows per employee and 'this month' and 'last month' Grade Levels.

(I had to play with the table names and column names a little to do this...)

EmptableJan:

LOAD * INLINE

[

Emp ID, Emp Name, Grade Level, Designation

1, Fred, 01, boss

2, Barry, 02, worker

3, Bill, 02, worker

4, Snedge, 03, lowlife

] ;

EmptableFeb:

LOAD * INLINE

[

Emp ID2, Emp Name2, Grade Level2, Designation2

1, Fred, 01, boss

2, Barry, 01, boss

3, Bill, 02, worker

4, Snedge, 02, worker

5, MooMoo, 03, lowlife

] ;

MergedEmps:

LOAD

[Emp ID],

[Emp Name],

[Grade Level] as [Prev Grade],

[Designation] as [Prev Des]

RESIDENT EmptableJan ;

ADD OUTER JOIN ( MergedEmps )

LOAD

[Emp ID2] as [Emp ID],

[Emp Name2] as [Emp Name],

[Grade Level2] as [New Grade],

[Designation2] as [New Des]

RESIDENT EmptableFeb ;

drop table EmptableJan ;

drop table EmptableFeb ;

Note that I've dropped the two 'Emptables' to avoid confusion with column names.

This way your first dimension can be

=if ( [Prev Grade] <> [New Grade], [Emp ID] )

...and for that dimension make sure you check "Suppress when value is NULL".

Now display the other details (previous/new grade/designations) as Expressions.

Anonymous · ‎2016-02-25

thank you for your work. the thing is i don't want to change the field names in those two tables.

I have already concatenated those two tables, where the data of those two tables are now in one single table, with a field name called [month], where i will split those data by hard coding [month name] as field name [month].

Is there any other way to do the required with this state.?

Report Inappropriate Content · ‎2016-02-25

Can you provide an example of this merged table?

Also, are Grade and Designation related?

Emp-tableJan: (50 rows)

Emp ID Emp name Grade Level Designation

01 abc 01 desga

02 xyz 02 desgx (Grade 02, Designation X, but it's B below)

03 ..... 02 .......

Emp-tableFeb: (60 rows)

Emp ID Emp name Grade Level Designation

01 abc 02 desgb (Grade 02, Designation B, but that's X above)

02 xyz 03 desgx (Grade 03 Designation X, but that's Grade 02 above)

03 ..... 02 .......

Before I can assist any further I'd like to see a good example of the data that you are trying to display. Also are you ONLY looking to display where the Grade has changed? Is the Designation of any consequence?

Anonymous · ‎2016-02-25

Grades and designation are not related. employee with different Grade Level can have same Designation, and employee data can be in any order in those two tables.

Emp-tableJan: (50 rows)

Emp ID Emp name Grade Level Designation Month

01 abc 01 desga Jan

02 xyz 02 desgx Jan

03 def 02 ....... Jan

Emp-tableFeb: (60 rows)

Emp ID Emp name Grade Level Designation Month

01 abc 02 desgb Feb

02 xyz 03 desgx Feb

03 def 02 ....... Feb

After Concatenation,

Emp-table AllMonth :

Emp ID Emp name Grade Level Designation Month

01 abc 01 desga Jan

02 xyz 02 desgx Jan

03 ..... 02 ....... Jan

01 abc 02 desgb Feb

02 xyz 03 desgx Feb

03 ..... 02 ....... Feb

Here, i have to display only those employees whose grade level has been changed, from month Jan to Feb with old and new grade level, like below in a pivot table or in any other way. but if its pivot table, it would be good for me.

EmpID Emp name Grade Level Month

01 abc 01 Jan

02 Feb

02 xyz 02 Jan

03 Feb

Report Inappropriate Content · ‎2016-02-26

In my humble opinion, the merge that you've done as actually made this a slightly less pleasant data set to work with, but hey-ho...

Add the following (checking field names, and table names, because the names you give to your tables is invalid in your examples, so I've had to change them) to your loader script.

RoleHasChanged:

LOAD

EmpID,

IF ( COUNT ( DISTINCT [Grade Level] ) > 1, 1, 0 ) AS HasChanged

RESIDENT EmpTableAllMonth

GROUP BY [EmpID] ;

This is creating a table of flags (just as Anton Aleksandrov suggested a lot earlier). You can now use that to control the presence of an expression...

Create a pivot with three Dimensions, EmpID, Emp Name and Grade Level.

One Expression:

if ( HasChanged, [Month4] )

Only those IDs where a Grade change has occurred will be present as all expressions (the only expression) in the table will be NULL for all those where the HasChanged flag = 0.

Anonymous · ‎2016-02-28

Thanks for your suggestion and help.

And may i know the reason and drawback of merge you have mentioned as a less pleasant data set to work with.?

As i am new to Qlikview i have some more doubts.

Consider i have four separate tables of employee details for four months, with same field names.

EmpJaninfo: (Table 1) ( January month - 50 records)

EmpID, Name, GradeLevel, Position, HireDate

001 John 02 abc 01/02/20012

EmpFebinfo: (Table 2) ( February month - 56 records)

EmpID, Name, GradeLevel, Position, HireDate

001 John 02 abc 01/02/20012

EmpMarinfo: (Table 3) ( March month - 60 records)

EmpID, Name, GradeLevel, Position, HireDate

001 John 03 xyz 01/02/20012

EmpAprinfo: (Table 4) ( April month - 66 records)

EmpID, Name, GradeLevel, Position, HireDate

001 John 03 xyz 01/02/20012

In this case, how do i load these tables in qlikview and will form a association with each other.?

And how do i differentiate these 4tables belongs to this particular month.?

Report Inappropriate Content · ‎2016-02-29

Ignore the 'less pleasant' comment. If that's the data you've got then so be it.

When you have the same named column in two tables, QlikView kinda creates a join between them.. In this instance you've got five identically named columns, identical structures in each table.

If you load these as they are then select File/Table Viewer from the menu, you're only going to see the first table that you load BUT it'll contain all of the values of all of the tables and you'll not know which month each row represents.

Load them like this, but add another column, 'TheMonth', or something like that, and set it to the month that the original table represents - just like you did previously.

This way the thing I showed earlier,

RoleHasChanged:

LOAD

EmpID,

IF ( COUNT ( DISTINCT [Grade Level] ) > 1, 1, 0 ) AS HasChanged

RESIDENT EmpTableAllMonth

GROUP BY [EmpID] ;

...should still work. But beware!... now you've got 'N' tables rather than two you're going to get N rows in the results for each employee whose role has changed. For example, in what you've got above, you're going to see John in every month because there are two distinct grades associated with his ID.