#2 How to create join for multiple resident Tables

[_google0]

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

[dwforest]

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]

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

[balabhaskarqlik]

Think so, you've to create Link table:

Check these:

https://community.qlik.com/blogs/qlikviewdesignblog/2012/10/26/mixed-granularity

http://www.learnallbi.com/link-table-in-qlikview/

https://www.analyticsvidhya.com/blog/2014/12/remove-synthetic-key-concatenation-link-table-qlikview/

[MK9885]

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]

Hi   Shahbaz and Bala,

Thanks responding, much appreciated !

Please find attached  #2QlikQuery..xls file for the problem statement

* sheet Script : Have attached Qlik script at my end if that need to copied

* Tab1,Tab2,Tab3,Tab4 - Sample data view

*Sheet IntenalTable Require: is table view which am looking for and trying built using join

Hope this help to guide, many thanks in advance

regards

x

[dwforest]

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]

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

[dwforest]

So load your mapping file directly....

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

[_google0]

done with some script changes , thanks