topic Re: To Find CrossBooked Function in QlikView

To Find CrossBooked Function

ankurakash — Tue, 22 Nov 2016 17:10:38 GMT

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

Re: To Find CrossBooked Function

Wed, 23 Nov 2016 05:20:27 GMT

Try the attached.

Re: To Find CrossBooked Function

ankurakash — Wed, 23 Nov 2016 07:03:31 GMT

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

Re: To Find CrossBooked Function

Wed, 23 Nov 2016 07:18:49 GMT

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.

Re: To Find CrossBooked Function

ankurakash — Wed, 23 Nov 2016 07:51:25 GMT

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.

Re: To Find CrossBooked Function

Wed, 23 Nov 2016 09:51:16 GMT

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.

Re: To Find CrossBooked Function

MK_QSL — Wed, 23 Nov 2016 09:52:34 GMT

Check this !

Re: To Find CrossBooked Function

ankurakash — Wed, 23 Nov 2016 12:49:12 GMT

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