Find out items on which two types of bookings apply ...

[datanibbler]

Hi,

I am working in a logistics company.

I have a report here to modify where there are several types of bookings, say A, B and C

- The users are actually interested only in booking_types B and C - but those are purely internal. They have no connection

   whatsoever to the delivering company.

- Now the users want to see the delivering company, however, and to link up that information to my DataModel, I need the records

  where the booking_type is A.

=> The combination of the two will only work in those cases where I have one of the two booking_types B or C - and also a booking

      of type A.

Thus, I need to find out those items for which there have been two bookings - either A and B or A and C.

I guess I will thus have to look up the AND_mode for listboxes, no? I will do so now - but maybe someone can lend me a little hand here - should I find it before, I will close this thread.

Thanks a lot!

Best regards,

DataNibbler

P.S.: The search_function on the Community is a bit difficult ...

[marcus_sommer]

Hi DataNibbler,

maybe a flag of the bookings within the datamodel could be useful, like in foloowing logic:

if(A and B and C, 1, if(A and B, 2, if(A and C, 3, if(B and C, 4, if(A, 5, if(B, 6, if(C, 7, 8)))))))

You might further need to wrap the checking with a len(trim()) or something similar and/or using a dual() to return also a string-content for the users, like: if(A and B and C, dual('A + B +C', 1), ...

- Marcus

[datanibbler]

Hi Marcus,

sounds good - only, that cannot be done so easily as the different booking_types are not all in the same record - there is one record for every booking (regardless of the type) that has been affected on any item.

Of course, if I had all the different bookings that have been effected on one item in one record, then I could quite easily determine whether I have the searched-for combination of different booking_types anywhere.

Maybe that can be done if I

- LOAD that transaction_table once for every different type of booking;

- Do an INNER JOIN between all of those tables

=> Then I could determine what type(s) of booking are present for any item.

Would that work? Worth a shot ...

Best regards,

DataNibbler

[marcus_sommer]

Hi DataNibbler,

instead of joins I would consider to use mapping(s) with applymap - you don't widened your table, it's fast and no risk of reducing/duplicating records through the join.

- Marcus

[datanibbler]

Oh - you mean I could

- first extract from my base_table all records where the boking_type was A (in all but few cases, there is only one

  such booking per item)

- then make two small mapping tables: One for all items where there was a booking of type B and one for all the items

   where there was a booking of type C

=> Then I could use my first load as base and use to Applymap() commands to find out whether, on any of the items

      that had a booking of type A also had a booking of type B or one of type C.

I will try that and be back here again for any news or with a new question.

Thanks a lot so far!

Best regards,

DataNibbler

P.S.: The INNER JOIN which I already tried between booking_types A and B resulted in 0 records - that is a bit surprising, I was expecting some items to show both types of bookings. Not all by a long way, but some.

[datanibbler]

Okay,

that worked - and it returned me quite a different result: Out of about 350 items on which bookings of type B were effected within the last 10 days, about 200 had a booking of type A, only about 150 did not.

I have to check whether my Mapping_table can work - there are two columns and the right one has to have unique values, no? Well, the left column are the item_IDs and I can just use some dummy value, like "1" as the second one - I only want to know whether or not that ID is found in that booking_A_table.

The next obstacle will be that I actually have to do this in a different tool 😉 I just wanted to try out the way to do it and whether it can be done at all, so this is a good starting point.

[marcus_sommer]

The mapping-values mustn't be unique but an applymap() will only return the first match (like an excel lookup) - so with an appropriate sorting it might be sufficient.

- Marcus

[datanibbler]

You mean the first match of the first column (the ID)? That one is unique. One ID cannot exist in our system twice. There can be many different bookings on one ID, but I have filtered for only one type before, so there should only be one record for every ID. The second column is just the fixed value '1' anyway - just to know whether or not

the ID on which that booking type B was performed also has a booking type A attached.

Well, I will try to do the same for the bookings type C - and then I have to think of how it would be possible to get those results in that other tool. That will be anotheer difficulty because QlikView gives me a lot of control over the process of loading the tables from the database and building my datamodel stepwise.

Best regards,

DataNibbler