topic Re: Cross Join 2 table in visualization in QlikView

Cross Join 2 table in visualization

gianluca_brick — Tue, 27 Sep 2022 09:55:10 GMT

Hi,
I have two tables that use different filters.
TABLE A and TABLE B joined by the PRODUCT_CODE field.
I would like to use the result of the two filters displayed in a single table. (I want cross join the two visualization into one).
Do you have any suggestions?
Attached an image that explains it.

Regards

Gianluca

Re: Cross Join 2 table in visualization

marcus_sommer — Tue, 27 Sep 2022 12:52:04 GMT

It looked as if the same information are distributed to two tables with different field-names unless your mentioned key-field. Such data-structure have most often massive disadvantages in regards to the performance and the usability in script and UI. Therefore I suggest to rethink the data-model and loading all facts into a single table by harmonizing the field-names.

- Marcus

Re: Cross Join 2 table in visualization

gianluca_brick — Tue, 27 Sep 2022 12:58:31 GMT

Unfortunately I can't load everything on a table because the query results come from two different filters. is it possible to know if there is a visual function that does the merge?

Gianluca

Re: Cross Join 2 table in visualization

marcus_sommer — Tue, 27 Sep 2022 13:14:36 GMT

That was the meaning - a single table and single selection-fields. Most things could be done in this way especially if in the script the sources of the different tables are added as extra fields and things like current/previous periods are flagged and/or the offset stored or similar measures.

- Marcus

Re: Cross Join 2 table in visualization

gianluca_brick — Tue, 27 Sep 2022 13:18:31 GMT

I also thought about this solution, but I cannot create a script to create a single table due to the filters on two different time frames and for other reasons. Thank you for your answer but at the moment I would need to understand if there is a function to use in the charts so that it does the cross join. 😞

Re: Cross Join 2 table in visualization

marcus_sommer — Tue, 27 Sep 2022 13:25:42 GMT

An association of the tables on a dimensional level is probably not possible or at least not without limitations and bigger efforts. But you may try it with the only key-field product as dimension and displaying all other information as expressions because each expression could query different fields against different conditions.

- Marcus

Re: Cross Join 2 table in visualization

gianluca_brick — Tue, 27 Sep 2022 13:35:03 GMT

ok Marcus,

but in expressions i will use the function to make cross join, how fuction I use for this?

Re: Cross Join 2 table in visualization

marcus_sommer — Tue, 27 Sep 2022 13:52:24 GMT

There is no function. The data are associated per the key-fields and the relationship of their data to each other. The essential work has to be done within the data-model. The UI objects are just a virtual image of these information.

Missing/wrong association from the data-model might be bypassed within the UI by creating a kind of cartesian product within the UI and then removing all wrong data and picking the wanted ones. That's not easy and quite often very slow and you may also hit various RAM limitations of the objects and/or running into any timeouts. I wouldn't go further with this approach else re-designing the application.

- Marcus