Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Selection issues/ scripting help

I posted this before, but it seems like the thread has died out. I will try to explain a little better here.

I have a document that contains around 30 tables. It was originally built for users to see information regarding orders. So The flow of the document begins with the Order head table and flows down to order details, jobs, shipments, and invoices. Other selection fields are also connected to the Order head table, like the Customer table for users to select customers and see their orders. Order details also has selection tables for part classes and for product groups.

We are also Multi company so part of the link from table to table is the company field. For user selection I am using the Company field from the Order head table.

With this set up everything was working correctly. Users select a company and see everything related to order regarding that company. The same is true for products, classes, and customers.

Now I have been requested to add Quote information to this document as well.

This is where I am having problems. There can be quotes that have no Orders, and Orders that are not related to quotes. They are linked from the quote detail table to the order detail table.

Users want the ability to select a company,customer,product,class (fields that quotes details and order details both have) and see ALL orders and ALL quotes from the selection.

Currently due to my selection tables/Company field linking into the Order head and order detail tables, when the user selects something they expect to see everything, but this is creating an left join to the quote and only pulling quotes that are linked to Orders.

In the other thread someone suggested concatenating the order tables with the quote tables to allow this to work, however this removes the link between quotes and orders. (They want to see a chart also that shows total quotes compared to what was ordered)

How could I go about doing this where they only need to select one field and see both sides of the data? Linking the customer to both quote head and to order head would create a circular reference, as would doing the same with the other selection tables.

Any help would be highly appreciated.

Attached is the table view. Circled in red are the tables/fields that I am having selection issues with.

15 Replies
Not applicable
Author

Hi,

with ref to you losing the link between quote and order this is why I said you should have one link table and not a detail link and a head link.  Give me a few mins and I'll see if I can do something with it.

Cheers,

Emma

Not applicable
Author

Hang on ... you've changed this quite a bit (joined quote info to order info so several tabled have now vanished).  I can't re-load the info as I don't have the source data.  Can you remove the joins and things you added or attach the original file?

Thanks,

Emma

Not applicable
Author

Here is a new sample file.

I created 2 link tables because the customers attach to the Head tables, and the Class/Product attach to the dtl tables. I do believe that there is a customer link in the detail tables so if you think this would make it work I can attach it to the details.

Not applicable
Author

Hi,

Have a look at the following and see if that might work.

You'll need to rename a couple of fields and then add the link table script in (I'd do this in a test copy if I were you) but it's difficult for me to check if it's linking everything as required as I don't know the data well enough.

I have my fingers crossed ...

Cheers,

Emma

Not applicable
Author

It seems like this finally did the trick. Thank you so much everyone for the help, this one was a major headache.

Not applicable
Author

🙂  Glad I could help!

Cheers,

Emma