topic Merging Two Calendar in QlikView

Merging Two Calendar

renuka_sasikumar — Wed, 10 Sep 2014 07:23:26 GMT

Hi,

I have Calendar Master, Sales, Service in Data Model where each n every tab is linked with Common Date.

But Even I have one Excel file where the fields are

LOAD SOrg. as SalesOrg,

DChl as Dist_Chnl,

Dv as Division,

Year,

Cycle,

Floor([Valid from]) as StartDate,

Floor([Valid to]) as EndDate,

[Reporting Month],

[Cycle Type]

FROM

(ooxml, embedded labels, table is Calendar);

From this Table I want to link wid the previous table How do i go about it.

Once wen I select any Date field its should pick from this excel and show the result according to it.

Thanks & Regards,

Renuka S

Re: Merging Two Calendar

er_mohit — Wed, 10 Sep 2014 07:36:47 GMT

use interval match functionality and also using either join or left join this table to previously loaded table

Re: Merging Two Calendar

vikasmahajan — Wed, 10 Sep 2014 10:14:40 GMT

Use interval MAtch as per Mohit said pfa sample

Tab1:

LOAD PROD_CODE,

PROD_BATCH,

SG_ID,

LOCCODE,

date(EFFDATE,'DD/MM/YYYY') AS EFFDATE

FROM

rate_updation.xlsx

(ooxml, embedded labels, table is BatchMaster);

Tab2:

LOAD

PROD_CODE,

PROD_BATCH,

PB_NBR,

date(PB_EFF_FROM,'DD/MM/YYYY') AS PB_EFF_FROM,

date(PB_EFF_TO,'DD/MM/YYYY') AS PB_EFF_TO

FROM

rate_updation.xlsx

(ooxml, embedded labels, table is RateMaster);

// where PB_PRICETYPE='P' and PB_ACTIVE=1 ;

MatchTable:

IntervalMatch (EFFDATE,PROD_CODE) //Match date first, then key(s) keys are now both in here

LOAD

DISTINCT

PB_EFF_FROM , //First two fields have to be the from date then the to date

PB_EFF_TO ,

PROD_CODE

Resident Tab2;

//Once the dates are matched, you can join other data onto the result of the interval match via the match date and the keys

Join (MatchTable)

LOAD

PROD_CODE,

PROD_BATCH,

// PB_PRICETYPE,

PB_NBR,

PB_EFF_FROM,

PB_EFF_TO

Resident Tab2;

DROP Table Tab2;

DROP Table Tab1;

Hope this help you

Vikas

Re: Merging Two Calendar

MayilVahanan — Wed, 10 Sep 2014 10:20:58 GMT

Please refer this

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch