Please See the script.
Brand:
LOAD item_code ,
item_code as ProdCode,
sales_stats_group_code as BrandCode,
stats_group_desc as BrandName
FROM
C:\Users\sandeepa_rout\Desktop\Brand_Data.txt
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
************************************************************************************************************************************
(Data from DataSource1)
IMS:
Directory;
LOAD @1 as IMS.CustCode,
@2 as IMS.TransType,
@3 as InvoiceNo,
@4,
Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as Week,
MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2)) as IMS.InvoiceDate,
Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as IMSWeek
FROM
[ExtractedIMSFiles(headers and details\Header\2010\07\271.txt]
(txt, codepage is 1252, no labels, delimiter is ',', msq);
HeaderJune281:
Directory;
Join
LOAD @1 as IMS.CustCode,
@2 as IMS.TransType,
@3 as InvoiceNo,
@4,
Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as Week,
MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2)) as IMS.InvoiceDate,
Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as IMSWeek
FROM
[ExtractedIMSFiles(headers and details\Header\2010\07\281.txt]
(txt, codepage is 1252, no labels, delimiter is ',', msq);
HeaderJune291:
Directory;
Join
LOAD @1 as IMS.CustCode,
@2 as IMS.TransType,
@3 as InvoiceNo,
@4,
Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as Week,
MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2)) as IMS.InvoiceDate,
Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as IMSWeek
FROM
[ExtractedIMSFiles(headers and details\Header\2010\07\291.txt]
(txt, codepage is 1252, no labels, delimiter is ',', msq);
HeaderJune301:
Directory;
Join
LOAD @1 as IMS.CustCode,
@2 as IMS.TransType,
@3 as InvoiceNo,
@4 ,
Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as Week,
MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2)) as IMS.InvoiceDate,
Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as IMSWeek
FROM
[ExtractedIMSFiles(headers and details\Header\2010\07\301.txt]
(txt, codepage is 1252, no labels, delimiter is ',', msq);
DetailJune271:
Directory;
Join
LOAD @1 as ProdCode,
@1 as IMS.ProdCode,
@2 as IMS.Qty,
@3 as IMS.LineValue,
num(round(@3, 1234.12)) as IMS.Amount,
@4 as IMS.InvType,
@5 as InvoiceNo
FROM
[ExtractedIMSFiles(headers and details\Detail\2010\07\271.txt]
(txt, codepage is 1252, no labels, delimiter is ',', msq);
DetailJune281:
Directory;
Join
LOAD @1 as ProdCode,
@1 as IMS.ProdCode,
@2 as IMS.Qty,
@3 as IMS.LineValue,
num(round(@3, 1234.12)) as IMS.Amount,
@4 as IMS.InvType,
@5 as InvoiceNo
FROM
[ExtractedIMSFiles(headers and details\Detail\2010\07\281.txt]
(txt, codepage is 1252, no labels, delimiter is ',', msq);
Detail291:
Directory;
Join
LOAD @1 as ProdCode,
@1 as IMS.ProdCode,
@2 as IMS.Qty,
@3 as IMS.LineValue,
num(round(@3, 1234.12)) as IMS.Amount,
@4 as IMS.InvType,
@5 as InvoiceNo
FROM
[ExtractedIMSFiles(headers and details\Detail\2010\07\291.txt]
(txt, codepage is 1252, no labels, delimiter is ',', msq);
Detail301:
Directory;
Join
LOAD @1 as ProdCode,
@1 as IMS.ProdCode,
@2 as IMS.Qty,
@3 as IMS.LineValue,
num(round(@3, 1234.12)) as IMS.Amount,
@4 as IMS.InvType,
@5 as InvoiceNo
FROM
[ExtractedIMSFiles(headers and details\Detail\2010\07\301.txt]
(txt, codepage is 1252, no labels, delimiter is ',', msq);
*************************************************************************************************************************************
(Data from DataSource2)
IMSReRun:
Directory;
LOAD @1 as CustCode,
@2 as TransType,
@3 as InvoiceNo,
@4 as InvDate,
Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as Week,
MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2)) as InvoiceDate,
Week(MakeDate(Left( @4,4), Mid(@4, 5, 2), Right(@4,2))) as IMSReRunWeek
FROM
[IMS-ReRun\TR727291-Header.txt]
(txt, codepage is 1252, no labels, delimiter is ',', msq);
Detail:
Directory;
Join
LOAD @1 as ProdCode,
@1 as IRR.ProdCode,
@2 as Qty,
@3 ,
num(round(@3, 1234.12)) as Amount,
@4 as InvType,
@5 as InvoiceNo
FROM
[IMS-ReRun\TR727291-Detail.txt]
(txt, codepage is 1252, no labels, delimiter is ',', msq);
************************************************************************************************************************************
(Calendar)
Data:
LOAD
// Year,
// Week,
[Start Date] as datum,
[Start Date],
[End date]
FROM
C:\Users\sandeepa_rout\Desktop\Book1.xlsx
(ooxml, embedded labels, table is ImsCalendar);
StartCalendar:
LOAD
Date(Floor(datum)) as datum
Resident Data;
Master_Calendar:
LET varMinDate = num(peek('datum' , 0 , 'StartCalendar'));
LET varMaxDate = num(peek('datum' , -1 , 'StartCalendar'));
LET vToday=num(today());
LET MinDate = peek('datum' , 0 , 'StartCalendar');
LET MaxDate = peek('datum' , -1 , 'StartCalendar');
LET vstart=date(0);
Temp_Calendar:
LOAD $(varMinDate) + rowno()-1 AS Num,
date($(varMinDate) + rowno()-1) AS TempDate
AUTOGENERATE $(varMaxDate)-$(varMinDate) +1;
Master_Calendar:
LOAD
TempDate,
TempDate AS datum,
week(TempDate) AS Week,
year(TempDate) AS Year,
month(TempDate) AS Month,
day(TempDate) AS Day,
weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
date(monthstart(TempDate), 'YYYY-MM') AS YearMonth,
week(TempDate) & '-' & year(TempDate) AS WeekYear,
inyeartodate(TempDate, $(vToday),0)*-1 AS CurYTDFlag,
inyeartodate(TempDate,$(vToday),-1) * -1 AS LastYTDFlag
RESIDENT [Temp_Calendar]
ORDER BY TempDate asc;
DROP TABLE Temp_Calendar,StartCalendar;
******************************************************************************************************************************************
Please suggest how to compare ProdCode of both the data sources..
Thanks
