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