Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ramshoney
Contributor

Regarding Joins in the Load Editor

Hi,

I have 3 Tables which loaded in my Script Editor.

Table1 with (TerritoryID,Area) with ( TerritoryID 55 Distinct Values )

Table2 with ( TerritoryID, TerritoryStae, PostCode ) with (TerritoryID 62 Distinct Values)

Table3 with (TerritoryID,Cust First Name,Cust Last Name,Sales Rep) with (TerritoryID 130 DIstinct Values )

I Made all the Tables Join with TerritoryID Column in the Load editor.

When I Create a Table report in the QlikSense, When I pull a Dimension column from TerritoryID (Expression editor fx) it is pulling all the 130 Dimension values into the Table report.

Issue1:

So i removed it and pull the column from Table1.TerritoryID (Expression editor fx) which im expecting only the 55 Distinct TerritoryID values, again it is showing the same 130 Distinct values even i'm pulling from Table1.TerritoryID.

Issue2:

Table1 with (TerritoryID,TerritoryArea ) with ( TerritoryID 55 Distinct Values )

Table4 with (TerritoryID,State) with (TerritoryID 42 Distinct Values )     all these 42 values exists in Table1

So when i Join The table as below im getting

[Table2]:

LOAD

    TerritoryID,

    state

FROM .....Table2.xlsx (ooxml, embedded labels, table is Sheet);

[Table1]:

LOAD

    Distinct  TerritoryID,

    Area

FROM ......... (qvd);;

Join ([Table1])

LOAD state as 'Terr State'

Resident [Table2];

When i ran the above in Table1

Im getting TerritoryID,Area,'Terr State'  with 225 Records. where i'm expecting 55 records.

3 Replies
omarbensalem
Esteemed Contributor

Re: Regarding Joins in the Load Editor

You want only the 55 territoryID that exist i,n table 1?

Table1:

load * from source1:

left join(Table1)

table2:

load * from source2;

left join(Table1)

Table3:

load * from source3;

ramshoney
Contributor

Re: Regarding Joins in the Load Editor

Omar Thanks for the Response.

Ya It should be when i pull Table1.TerritoryID from ( Expression editor ) it should show only 55 Values )

Ya It should be when i pull Table2.TerritoryID from ( Expression editor ) it should show only 62 Values )

Ya It should be when i pull Table3.TerritoryID from ( Expression editor ) it should show only 130 Values )

as We know when we can see dropdown list we can able to see only one TerritoryID , when i pull it in the table report, I'm seeing all the 130 Values.

omarbensalem
Esteemed Contributor

Re: Regarding Joins in the Load Editor

In that case ;add a flag in the 3 tables:

I mean:

Table1:

load *,

'table1' as Flag

from source1:

table2:

load *,

'table2' as Flag from source2;

Table3:

load *,

'table3' as Flag from source3;

and in your expression; if want to only work with fields from table1; you add this Flage,

Example:

if(Flag='table1', TerritoryID, null()) as a dimension?

Try it