Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ankurakash
New 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
New Contributor III

Re: To Find CrossBooked Function

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

7 Replies
Not applicable

Re: To Find CrossBooked Function

Try the attached.

Highlighted
ankurakash
New Contributor III

Re: To Find CrossBooked Function

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

Re: To Find CrossBooked Function

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
New Contributor III

Re: To Find CrossBooked 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.

Not applicable

Re: To Find CrossBooked Function

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

Check this !

ankurakash
New Contributor III

Re: To Find CrossBooked Function

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