Week range mapping

[GOKULAKANNAN]

Hi ,

I have a requirement where i will get the data from one table like below

Load * inline [

Week1,week2,Quarter

1,13,Q1

14,26,Q2

27,39,Q3

40,52,Q4

];

Now i want to map this another table which is having week as column with values 1 to 52,

If i select week 2 it should show category as Q1 respectively, how can i acheive that

[SunilChauhan]

try intervalMtach , which may solve you problem

[GOKULAKANNAN]

I dont have  any key field, in table 1 im having week and emp name, in table 2 am having, week1,2 and quarter.

[MarcoWedel · Accepted solution]

Hi,

you could just calculate the Quarter from the Week number itself, e.g. like

'Q'&(Div(Week-1,13)+1) as Quarter

If you really want to use this Week/Quarter table instead, some possible solutions could be (just for scripting reference):

• Expanding the Week ranges and using ApplyMap:
  

  mapWeeksQuarter:
  Mapping
  LOAD Week1+IterNo()-1,
       Quarter 
  Inline [
  Week1,Week2,Quarter
  1,13,Q1
  14,26,Q2
  27,39,Q3
  40,52,Q4
  ]
  While Week1+IterNo()-1 <= Week2;
  
  
  tabOther:
  LOAD RecNo() as Week,
       ApplyMap('mapWeeksQuarter',RecNo()) as Quarter,
       Ceil(Rand()*100) as SomeRandomFact
  AutoGenerate 52;​

• Expanding the Week ranges and joining:
  

  tabOther:
  LOAD RecNo() as Week,
       Ceil(Rand()*100) as SomeRandomFact
  AutoGenerate 52;
  
  Left Join
  LOAD Week1+IterNo()-1 as Week,
       Quarter 
  Inline [
  Week1,Week2,Quarter
  1,13,Q1
  14,26,Q2
  27,39,Q3
  40,52,Q4
  ]
  While Week1+IterNo()-1 <= Week2;​

• interval matching the weeks to week ranges:
  

  tabOther:
  LOAD RecNo() as Week,
       Ceil(Rand()*100) as SomeRandomFact
  AutoGenerate 52;
  
  tabWeeksQuarter:
  LOAD * Inline [
  Week1,Week2,Quarter
  1,13,Q1
  14,26,Q2
  27,39,Q3
  40,52,Q4
  ];
  
  Left Join (tabOther)
  IntervalMatch (Week)
  LOAD Week1,
       Week2
  Resident tabWeeksQuarter;
  
  Left Join (tabOther)
  LOAD * Resident tabWeeksQuarter;
  
  DROP Fields Week1, Week2;
  DROP Table tabWeeksQuarter;​

  
  hope this helps
  
  Marco