Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 .
