topic Re: qliksense conditional select while joining tables in App Development

qliksense conditional select while joining tables

beaubellamy — Sat, 16 Nov 2024 04:55:16 GMT

I would like to recreate the SQL command below.

I am unable to use the data connection directly from Qliksense to the datebasse, we are still troubleshooting this. In the meantime, i am loading the tables as csv files into the data load editor and wanting to select the tables based on the below SQL command

select *
from workorder
left join asset on workorder.assetnum = asset.assetnum
left join classancestor on asset.classstructureid = classancestor.classstructureid
left join pm on asset.assetnum = pm.assetnum
left join locations on asset.location = locations.location
where classancestor.hierarchylevels = 0
AND classancestor.ancestorclassid = 'TURNOUT'

Re: qliksense conditional select while joining tables

Claudiu_Anghelescu — Tue, 20 Aug 2019 06:48:02 GMT

Store the raw data from .csv into .qvd and then make the join using the .qvd tables

Re: qliksense conditional select while joining tables

beaubellamy — Tue, 20 Aug 2019 06:56:24 GMT

I think i understand how to join the table in the editor (i found this https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/Scripting/combine-tables-join-keep.htm), but how do i apply the where clause?

Re: qliksense conditional select while joining tables

Claudiu_Anghelescu — Tue, 20 Aug 2019 06:59:13 GMT

You have to create a temporary table with the LEFT JOINS and after this to import the data from the temporary table into a new table.

TEMP:

LOAD *

LEFT JOIN

....;

FINAL_TABLE:

LOAD *

RESIDENT TABLE TEMP

WHERE ..... ;

Re: qliksense conditional select while joining tables

marcus_malinow — Tue, 20 Aug 2019 07:41:04 GMT

Given that your key fields are named identically in each of your source tables this should be relatively simple.

The final right join to ClassAncestor will take care of your where clause

[Table]:

LOAD

[field list]

FROM [Work Order];

LEFT JOIN (Table)

LOAD

[field list]

FROM [Asset];

LEFT JOIN (Table)

LOAD

[field list]

FROM [PM];

LEFT JOIN (Table)

LOAD

[field list]

FROM [Locations];

RIGHT JOIN (Table)

LOAD

[field list]

FROM [ClassAncestor]

WHERE hierarchylevels = 0
AND ancestorclassid = 'TURNOUT';