topic Joins and filter issues in QlikView

Joins and filter issues

Thu, 29 Jul 2010 18:00:04 GMT

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

Joins and filter issues

pkelly — Thu, 29 Jul 2010 20:37:51 GMT

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...

Joins and filter issues

Fri, 30 Jul 2010 15:23:46 GMT

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.

Joins and filter issues

pkelly — Fri, 30 Jul 2010 15:37:17 GMT

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.

Joins and filter issues

Fri, 30 Jul 2010 15:45:06 GMT

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)

Joins and filter issues

pkelly — Fri, 30 Jul 2010 15:55:01 GMT

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.

Joins and filter issues

pkelly — Fri, 30 Jul 2010 15:59:57 GMT

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...

Joins and filter issues

Fri, 30 Jul 2010 16:59:11 GMT

Hmm.. thanks for the suggestion.

Joins and filter issues

Fri, 30 Jul 2010 21:30:14 GMT

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)

Just put the - in tblworkorder

and do that 'Select Excluded' it will work.

Regards

Immutable

Joins and filter issues

Fri, 30 Jul 2010 21:33:25 GMT

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

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

Joins and filter issues

Fri, 30 Jul 2010 21:41:58 GMT

Thanks DJPDon for elaborating the answer.

Joins and filter issues

pkelly — Fri, 30 Jul 2010 22:58:30 GMT

The solution given by the guys above will work for the example data that you have provided.

I am assuming that the data you have provided above is a data dump from the tables within your qvw file and the dash is in fact representative of a null value so in reality, you probably won't be able to make the manual change as described above in the actual report i.e. you will be pulling new data when this is reloaded...apologies if I am wrong in this assumption...

Going back to my earlier suggestion, what you could do as part of your script is use an if statement to replace null wo numbers with a value.

I suggested 999 but this could be anything...

I have attached a qvw file...I have tried to suggest a route and offer my apologies if |I am way off the mark - I am relatively new to QlikView....

Good luck...

Joins and filter issues

Sat, 31 Jul 2010 16:21:07 GMT

Hey Paul,

Your solution also work however, if we have millions of rows then we have to replace - by 999 it will be a lot of work.

In my solution jus add the - to other table so that it will identify it and it will work for all the million rows.

I am new to this technology i am still exploring it. Its funnnnn......

Regards

Vikas Pathak

Joins and filter issues

pkelly — Sun, 01 Aug 2010 13:07:33 GMT

Hi Vikas...

Agree, QlikViw is great fun...

I agree that just adding - to the other table is easier than doing it as part of the script.

My question would be on the mechanism for doing this.

This looks like a manual solution so everytime the data is reloaded, someone has to manually add this to the table.

Think it is always better to look for an automated solution.

This however may be the solution that hangiamm is looking for....