Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ankurakash
Contributor III
Contributor III

To Find CrossBooked Function

Hi Qlikers,

I have a scenario where I have a master data containing Function and the Area the function is mapped to.

I have a resource data which has resources booked their hours against the Functions they are part to.

I need to find how many fuction are crossbooked ? For Ex.

in, Master table: Function A can only be mapped to Area 'ADAD' and 'ODOD'

however, in Resource data, A resource has filled his hours against Function A in Area 'ADAD', 'ODOD' and 'MODI'

In this case, the no. of hours booked under Function A for Area 'MODI' will be called Crossbooked hours

'lly, Also need to get in the result

1) No. of function crossbooked (in case of shared excel, the correct answer would be 2)

2) Count. of resources crossbooked

I tried with creating a common key approach and then matching the key. I am not getting the result.

Attached is the sample excel file for the above scenario.

Request your help.

Regards,

Ankur

1 Solution

Accepted Solutions
ankurakash
Contributor III
Contributor III
Author

Hi Sunil,

I tried with this below logic and it worked well. Your idea influenced me to get the desired result.

I did the following:

MainTable:
LOAD
  Function&Area as Key,
  ResId,
     ResName,
     ResCategory,
     Hours,
     Function,
     Area  
FROM
[Sample Mapping Table.xlsx]
(ooxml, embedded labels, table is ResourceData);

left join (MainTable)
LOAD
     Area as CheckColumn,
     Function&Area as Key
FROM
[Sample Mapping Table.xlsx]
(ooxml, embedded labels, table is [Master Table], filters(
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1)),
Remove(Row, Pos(Top, 8))
));

Final:
NoConcatenate
Load
*,
IF(Key1=Key2, 'X',Hours) as Crossbookedhours
Resident
MainTable;

Drop Table MainTable;

In Pivot table I did this:
Dim: Function ; Dim: Area(Transposed)
Exp: IF(Sum(Crossbookedhours) = 0, 'X', Sum(Crossbookedhours))

Thank you for your support and timely help.

Regards,

Ankur

View solution in original post

7 Replies
Not applicable

Try the attached.

ankurakash
Contributor III
Contributor III
Author

hi Sunil,

The crossbooked hours are coming correctly. Thanks. however when i verified the correct hours column, it don't have the correct numbers as per the shared xcel. Also, is it possible to display correct hours as 'X'.

And the function field(dim) to be transposed under which, if correct hours(display 'X') or crossbooked hours 'display numbers'.

Regards,

Ankur

Not applicable

My bad for correct hours , i labelled it incorrectly, it was total hours. Do you want to display a character 'X' only in correct hours no numerical values?

if this is the scenario then have a look in attachment. I have transposed the function.

ankurakash
Contributor III
Contributor III
Author

Also,

I am matching the source data and master data based on key by creating key1 and key2. And not with the checkcolumn because it may not work in all the scenarios.

however my approach of matching key from both the tables is not working.

Not applicable

Please tell me the case where it will not work or send me your app or the script so i can have a look into it why it is not working.

MK_QSL
MVP
MVP

Check this !

ankurakash
Contributor III
Contributor III
Author

Hi Sunil,

I tried with this below logic and it worked well. Your idea influenced me to get the desired result.

I did the following:

MainTable:
LOAD
  Function&Area as Key,
  ResId,
     ResName,
     ResCategory,
     Hours,
     Function,
     Area  
FROM
[Sample Mapping Table.xlsx]
(ooxml, embedded labels, table is ResourceData);

left join (MainTable)
LOAD
     Area as CheckColumn,
     Function&Area as Key
FROM
[Sample Mapping Table.xlsx]
(ooxml, embedded labels, table is [Master Table], filters(
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1)),
Remove(Row, Pos(Top, 8))
));

Final:
NoConcatenate
Load
*,
IF(Key1=Key2, 'X',Hours) as Crossbookedhours
Resident
MainTable;

Drop Table MainTable;

In Pivot table I did this:
Dim: Function ; Dim: Area(Transposed)
Exp: IF(Sum(Crossbookedhours) = 0, 'X', Sum(Crossbookedhours))

Thank you for your support and timely help.

Regards,

Ankur