Help to do data model for below logic.

[debabrata_sahoo]

Find the below 2 table and let me help how will i do data model. In Front end I need to show Sum(weight) with using filter SHC GROUP CODE .

Table-1(FACT)

AWB	SHC	WeightKg
111	BUP,BUC,COU,EAP	356
121	COL,PHC,PIL	456
131	COL,HEA,PHC,PIL	378
141	CRT,PER,PHC,SPX	124

Table-2

SHC	SHC GROUPCODE
BUP	1
BUC	2
COU	3
EAP	1
COL	2
PIL	3
CRT	1
PER	4
PHC	5
SPX	5
HEA	3

[cbaqir]

I think you are asking how to join the tables. You can use subfield(SHC,',') on table 1 to parse out each.

Table1:

Load

AWB,

subfield(SHC,',') as SHC,

WeightKg

FROM

Table1 Source;

Table2:

Load

SHC,

[SHC GROUPCODE]

FROM

Table2 Source;