Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Joining Issues

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

1 Reply
Not applicable

Re: Joining Issues

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