Displaying records only for satisfied condition

[Anonymous]

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.

[Anonymous]

might be helpful for you?

Slowly Changing Dimensions

IntervalMatch and Slowly Changing Dimensions

[asgardd2]

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

Please, attach a sample.

[#4]

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]

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.?

[#6]

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]

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

[#8 · Accepted solution]

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]

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.?

[#10]

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.