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: 
_google0
Contributor
Contributor

#2 How to create join for multiple resident Tables

Hi All,

Am looking to create new table based on resident tables ( ResidentTab1,ResidentTab2,ResidentTab3), not sure if i need to use join or keep left join statement and grouping

RTab1 is running calendar week ,

RTab2 is required volume for products for specific week ( e.g.2017-WK 30, Product A,100)

RTab3 is for week in which product is packed and week in which it is available (e.g. 2017-W26 Product A Pack.Vol 25 PassWK2017-W29)

What i am looking to achieve is use RTab1 and join RTab2 ,RTab3 also use SUM ( Pack.Vol) when PassWK is equal to Calendar week, why SUM since packed volume will be split in lots for product thus sum product lots quantity for next calculation.

Have attached example of table structure in attached text file,there may be  better way to achieve what am trying ..

ResidentTab1
CYear
CYearQuarter
CYearMonth
CYearWeek

ResidentTab2
CYear
Req.WK
Product
Req.Vol

ResidentTab3
Pack.WK
Pack.Vol
PassWK

(Creat new table  based on join)
CYear
CYearQuarter
CYearMonth
CYearWeek
Product
Req.Vol
Pack.Vol

9 Replies
dwforest
Specialist II
Specialist II

You want to JOIN the tables, Qlik will do this automatically based on the field names that are the same.

You want to rename the fields in Table 2 and 3 so they match Table1 fields, e.g Pack. WK as CYearWeek. Of course the values should be the same type; e.g. Week = 1 or  Week = {Start of week date} in all tables.

_google0
Contributor
Contributor
Author

Hi David,

Thanks for reply and sorry missed product key previous post, yes this resident tables have automatic join  Tab1~Tab2 =CYear & Tab2~Tab3= Product

What i am looking to create is new table based on these resident tables  - as below

CYear, YearQuarter,YearMonth,YearWeek,Product,Req.Vol,Pack.Vol

ResidentTab1

CYear, YearQuarter,YearMonth,YearWeek

ResidentTab2
CYear,Req.WK,Product,Req.Vol

ResidentTab3

Product,Pack.WK,Pack.Vol,PassWK

MK9885
Master II
Master II

Not sure if this will work but maybe try below or if you can give us the sample data in XL or add Inline to your .xt file

Fact:

Load

CYear,

CYearQuarter,

CYearMonth,

CYearWeek,

From......;

left join (Fact)

Table1:

Load

CYear,

Req.WK,

Product,

Req.Vol

Resident ResidentTab2;


left join (Fact)


Table2:

Load

subfield(Pack.WK, '-', 1) as CYear,

Pack.Vol

Resident ResidentTab3;


_google0
Contributor
Contributor
Author

Hi

dwforest
Specialist II
Specialist II

You want a more complete join than just Year, if your data is at the week level and probably week level in addition to to Product.

Your current script does not create a new table, just joins the separate tables based on matching name fields; you want to create JOIN statements to create the new table.

Something like:


[MapBatch]:

Mapping load

[MapBatch],

[PassWK]

Resident Tab4;



[DesiredTable]:

LOAD

[CYear],

[CYearQuarter],

[CYearMonth],

[CYearWeek]


FROM [lib://AttachedFiles/#2QlikQuery.xlsx]

(ooxml, embedded labels, table is Tab1);


JOIN


LOAD

[CYear],

[Req.WK] AS CYearWeek,

[Product],

[Req.Vol]


FROM [lib://AttachedFiles/#2QlikQuery.xlsx]

(ooxml, embedded labels, table is Tab2);


JOIN

LOAD

[Pack.WK] AS CYearWeek,

[Product],

[Batch],

[Pack.Vol],

ApplyMap('MapBatch',[PassWK]) as [MatchPassWK]

    

FROM [lib://AttachedFiles/#2QlikQuery.xlsx]

(ooxml, embedded labels, table is Tab3);


JOIN


LOAD

[Product],

[Batch] as [MapBatch],

[PassWK] AS CYearWeek

FROM [lib://AttachedFiles/#2QlikQuery.xlsx]

(ooxml, embedded labels, table is Tab4);

No need to label the tables as you will end up with a single table containing all the columns joined on the keys where the names match.

_google0
Contributor
Contributor
Author

Hi David,

thanks for response, however i get error on load script, error is because Tab4 load at end of the script thus Mapping load on resident Tab4 at start will not work.

In the earlier attached file i had place tab4 before Tab 3 to have mapping sequence and there was no error.

Also "IntenalTable Require - Pack.Vol " is Sum at Product level (i.e in Tab3 Pack.Vol is at product &Batch) thus

some how i need to arrive at SUM of Pack.Vol where Product =Product Tab3 & CYearWeek = ",[PassWK]) as [MatchPassWK]"

In excel i had retained the formula for IntenalTable Require - Pack.Vol

ErrorQlik.jpg

dwforest
Specialist II
Specialist II

So load your mapping file directly....

Add a Sum and grouping as appropriate or create a Sum() measure in the app.

_google0
Contributor
Contributor
Author

done with some script changes , thanks