topic Re: Week range mapping in App Development

Week range mapping

GOKULAKANNAN — Thu, 15 Jun 2023 06:57:24 GMT

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 — Thu, 15 Jun 2023 09:51:58 GMT

try intervalMtach , which may solve you problem

GOKULAKANNAN — Thu, 15 Jun 2023 14:06:23 GMT

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 — Thu, 15 Jun 2023 20:32:23 GMT

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