Joins and filter issues

[#1]

Hi,

I'm trying a to create a report and need some help because I'm having issues with my report. I have two tables - tblworkorder and tbltime (joined by idworkorder). I have 2 types of time - Direct Labor which has a Work Order related to the time charge and Indirect Labor which does not have a Work Order (ex. sick leave, or holidays). 95% of the time charges have a work order and 5% do not.

The problem I'm having is that once I cannot get the indirect labor records to show in my table box if I highlight/select any workorders from the list. Is there anyway I can still get it to show both the direct and indirect labor to show?

Thanks in advance

[pkelly]

hi...

Apologies if I have misunderstood what you are saying here.

If you select a works order from a list box, you will only see the time that is associated with that works orders i.e. direct labour.

No indirect labour will be shown as none is associated with the selected works order.

What indirect labour values would you expect to see when you select a works order?

Again, apologies if I have picked this up wrongly...

[EDIT]

It might be worthwhile posting an example...

[#3]

Hi Paul,

Thanks for the response. An example will actually make it easier to understand.

tbltimecharge
tcID, tcName, tcHours, tcType, tcWOID
01, James, 3, regular, 303
02, James, 5, regular, 304
03, James, 2, regular, 305
04, James, 8, sick, -
05, Anne, 8, holiday, -
06, Anne, 5, holiday, -
07, Anne, 1, regular, 305

tblworkorder
woID, woNumber, woDescription
303, 395164, Repair something
304, 395165, Repair something else
305, 395166, Conduct Inspection

Both tables are automatically joined by Qlikview on woID. I've created a report to show both tables (select fields) and a table box showing all fields in both tables. The result is as follows

TableBox
tcID, tcName, tcHours, tcType, tcWOID, woNumber, woDescription
01, James, 3, regular, 303, 395164, Repair something
02, James, 5, regular, 304, 395165, Repair something else
03, James, 2, regular, 305, 395166, Conduct Inspection
04, James, 8, sick, -, -, -
05, Anne, 8, holiday, -, -, -
06, Anne, 5, holiday, -, -, -
07, Anne, 1, regular, 305, 395166, Conduct Inspection

Here is the problem: I want to see all time charges except for work order 395166 including regular, sick & holiday tcType. tcID 01, 02, 04, 05, & 06 should be the result in the table box. If I were to type not equal to workorder 395166, then it only shows tc id 01, and 02 - filtering out the other records which I need (04, 05, and 06)

Unless I explicitly select and highlight only those tcID's for the sample above, then it works but the real db has about 1,100,000 records.

[pkelly]

This may work for this specific example...

Create a list box with you works orders in it.

Select 395166 so that you have in fact filtered on 395166.

If you then right click and click "Select Excluded"...

This will then deselect 395166 then select all other works orders.

[#5]

Hi Paul. I have tried that too but the problem with doing so is that it will only look for timecharges that have a work order. I want it to also include indirect timecharges (holiday and sick)

[pkelly]

Is it possible that you could post an example qlikview file?

Appreciate you have outlined the structure but this allows us to play around a bit more and visually see the problem that you have....

EDIT

Please ignore this request - just noticed that we can create from data supplied above.

[pkelly]

Unsure if this will work or not...

In your script, you could allocate a ficticious works order number to these time changes say 999...

Not ideal but it may work for you...

[#8]

Hmm.. thanks for the suggestion.

[#9]

just put a - in tblworkorder above the curly brackets . Earlier it was not realting to the idtimecharge table. in the chart put the expression if(woNumber<>391566, woNumber)

OR

Just put the - in tblworkorder

and do that 'Select Excluded' it will work.

Regards

Immutable

[#10]

---------------------------------------------------------------------------------------

TIMECHARGE:

LOAD * INLINE [

ID, Name, Hr, Type, WOID

1, James, 3, Regular, 303

2, James, 5, Regular, 304

3, James, 2, Regular, 305

4, James, 8, Sick, -

5, Anne, 8, Holiday, -

6, Anne, 5, Holiday, -

7, Anne, 1, Regular, 305

];

WORKERS:

LOAD * INLINE [

WOID, WONUMBER, WODESCRIPTION

303, 395164, Repair Something

304, 395165, Repair something else

305, 395168, Conduct Inspection

-

];

------------------------------------------------------------------------------------

Here u just have to add a '' - " in your WOID column in WORKER table .

check above script with same data that u provided,

select the field and select excluded to verify the answer

Thank You,

Patel