Joining Issues

Wed, 28 Sep 2011 15:20:43 GMT

Hi All,

Please find attached of the screen shot of my schema.

In this screen shot you can able to see there are 4 tables.

1- IMS2010 2-IMSReRun 3-IMSCalendar 4-Brand Data

IMS2010 is coming from IMS Data Source and IMSReRun table is coming from SLS DataSource. It has same sets of field.

I have to compare each other fields in regards of ProdCode so that one could easily know which data is missing in which DataSource.

By keeping it simple I have to compare the data of IMS2010 and IMSReRun against ProdCode.

Hope it makes sense.

Please suggest .....!

Many Thanks,

Sandeepa

Re: Joining Issues

Thu, 29 Sep 2011 05:30:31 GMT

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

topic Joining Issues in QlikView

Joining Issues

Re: Joining Issues