how to link incremental tables with excel tables?

[riishabhz]

please look at images of my script:-

first table is incremental table of sql

second table is incremental table of sql

third table is multiple excel files which i took help from https://community.qlik.com/t5/New-to-QlikView/load-multiple-txt-files-using-loop/m-p/1618971#M380105   @rwunderlich

fourth table is also excel files but single excel file only.

now i want to link tables or join all of them for my project.

please help me.

my script is :- (Bold highlights are table name)

max:
LOAD max (TransactionDate) as TransactionDate

FROM VWStockMovement_Qlik.qvd (qvd);

let vamxdater=peek('TransactionDate',0,'max');

drop table max;
final:
LOAD
//NCode&'-'&PartnerCode&'-'&Channel&'-'&ETPCode&'-'&"Doc_Type"&'-'&Season&'-'&Style&'-'&Color&'-'&Size&'-'&ETPMRP as [key],
NCode,
PartnerCode,
Channel,
ETPCode,
TransactionDate,
"Doc_Type",
TransactionNo,
Season,
Style,
Color,
Size,
TransactionQuantity,
ETPMRP;
SQL SELECT *
FROM ETPEASV55.dbo."VWStockMovement_Qlik" where TransactionDate>'$(vamxdate)';

Concatenate(final)
LOAD NCode,
PartnerCode,
Channel,
ETPCode,
TransactionDate,
Doc_Type,
TransactionNo,
Season,
Style,
Color,
Size,
TransactionQuantity,
ETPMRP
FROM
VWStockMovement_Qlik.qvd
(qvd);

store final into VWStockMovement_Qlik.qvd(qvd);
max1:
LOAD max (SalesDate) as SalesDate

FROM [..\VWCashOrderTRN_Qlik\VWCashOrderTRN_Qlik.qvd]
(qvd);

let vamxdater=peek('SalesDate',0,'max');

drop table max1;
//Concatenate(final)
final1:
LOAD
//NCode&'-'&PartnerCode&'-'&Channel&'-'&ETPCode&'-'&"Doc_Type"&'-'&Season&'-'&Style&'-'&Color&'-'&Size&'-'&ETPMRP&'-' as [key],
NCode,
PartnerCode,
Channel,
ETPCode,
SalesDate,
"Doc_Type",
BillNo,
CustomerNumber,
Season,
Style,
Color,
Size,
InvoiceQuantity,
ETPMRP,
RealisedSale,
TAXAmount,
TAXRate,
NETSale,
SalesPerson;
SQL SELECT *
FROM ETPEASV55.dbo."VWCashOrderTRN_Qlik" where SalesDate>'$(vamxdate)';

Concatenate(final1)
LOAD NCode,
PartnerCode,
Channel,
ETPCode,
SalesDate,
Doc_Type,
BillNo,
CustomerNumber,
Season,
Style,
Color,
Size,
InvoiceQuantity,
ETPMRP,
RealisedSale,
TAXAmount,
TAXRate,
NETSale,
SalesPerson
FROM
[..\VWCashOrderTRN_Qlik\VWCashOrderTRN_Qlik.qvd]
(qvd);

store final1 into [..\VWCashOrderTRN_Qlik\VWCashOrderTRN_Qlik.qvd]
(qvd);

material:
LOAD MATERIAL,
COLOR,
SIZE,
[Size Group],
Description,
COMPOSITION,
BRANDCD,
BRAND,
SEASON,
SEASONDESC,
THEME,
DELYCODE,
GENDER,
ProductHierarchy,
MaterialGroup,
[Core/Fashion],
FIT,
PATTERN,
[Key/NonKey],
RANGE,
ORIGIN,
[Top/Bottom],
SLEEVE,
NECK,
WEATHER,
OCCASSION,
CustomDesc,
MaterialGrp4,
MGRP4DESC,
EAN,
ALTEAN,
PurchaseGroup,
PurchaseGroupDesc,
CATEGORY,
SUBCAT,
BASECOMP,
MaterialType,
ValuationClass,
COST,
MRP,
ChangeDt,
RowNo() as RecId
FROM
[C:\Users\dmsadmin\Desktop\riishabhz\New folder\MAT_MAS\material\MAT_MAS_*.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq) ;

Inner Join(material)
LOAD EAN, Max(RecId) as RecId
Resident material
Group by EAN;
DROP FIELD RecId;

customer:
LOAD Customer,
Name1,
Name2,
Street,
City,
PostalCode,
District,
Country,
Region,
Telephone1,
Telephone2,
FaxNumber,
CustomerClassific,
VAT_Regn_No,
Condition_group4,
ShopID_Sem,
Sales_Organization,
Distribution_Channel,
Division,
Price_List,
Payment_Type,
Incoterms,
Incoterms_Part2,
Delivery_Priority,
Currency,
Payment_Terms,
Delivering_Plant,
Sales_Group,
Sales_Office,
Grp6,
Grp8,
ShopSurface,
Shop_Postion,
Shop_Opening_Year,
Redecoration_Date,
No_of_Floors,
No_of_Shop_Window,
Shop_Surface,
Stock_Room_Surface,
Interior_Designer,
Shop_Location,
Octroi_To_Be_PaidBy,
ShopOpeingDate,
ShopClosingDate,
BillTo,
BillToName,
BillToState,
BillToVAT,
ShipTo,
ShipToName,
ShiptoState,
ShipToVAT,
Date,
[UCB Data Check],
Tier,
[Store Ranking],
[Operating Cost],
[Outlet Store],
[Active Flag],
[Store Flag],
[Store Class]
FROM
[..\New folder\MAT_MAS\customer\CUST_MAST.txt]
(txt, utf8, embedded labels, delimiter is '|', msq);

any help please .

[wade12]

the qlik engine will automatically associate tables whenever 2 (or more) tables contain the same field name.

a).   if 2 tables do not have any field names in common then these 2 tables will not be directly associated with each other.

if you need them to be associated then you will have to be creative with your field names.

b).  if 2 (or more) tables have more than 1 field name in common then a synthetic key will be created

... & should be resolved.

[riishabhz]

Yes sir i know about that but i want to link all my 5 tables, moreover its
an incremental tables so i am getting problem in it

[wade12]

please save each table into a qvd and share, thanks.