qliksense conditional select while joining tables

[beaubellamy]

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'

[Claudiu_Anghelescu]

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

[beaubellamy]

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...), but how do i apply the where clause?

[Claudiu_Anghelescu]

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

LEFT JOIN

....;

FINAL_TABLE:

LOAD *

RESIDENT TABLE TEMP

WHERE ..... ;

[marcus_malinow · Accepted solution]

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';