Issue With DATA MOdel

[abubakarsiddiq7]

Hi Experts,

I need help on below requirement .

T1:

Load * Inline [

ID,YEARMONTH,Sales

1,202101,1000

2,202102,2000

3,202103,3000

];

T2:

Load * Inline [

ID,SM

1,123

2,234

3,342

];

T3:

Load * Inline [

REPORTMONTH,SM,Target

202101,123,1500

202102,234,2400

202103,341,3500

];

I have 3 tables and I need SM wise sales and Target

Please help me with the data model here

[AshutoshBhumkar]

Hello,

So when you load these tables,below associations will be created.

T1 and T2 will be linked on ID field.

T2 and T3 will be linked on SM field.

So when you create a table, you will get data on the basis of matching data. In your case it will be the sales for SM 123 and 234 as thease two rows are matching with the T2 table.

Help us understand the exact requirement if any, so that you will get the correct help.

Thanks,

Ashutosh

[abubakarsiddiq7]

Report month and yearmonth are not equal when I am doing the above solution values are not matching 

[AshutoshBhumkar]

Could you provide the expected output ?

[abubakarsiddiq7]

I have to give yearmonth filter .when I am doing the above solution targets and sales are not matching 

[abubakarsiddiq7]

I'd, yearmonth, target, sales 

1,202101,1500,1000

2,202102,2500,2000

Like this

[AshutoshBhumkar]

So as per the expected output, what I understood is.

1. You are taking data present only in T2 table. (123,234 in your case)

2. Taking only ID which matched with the SM in T2. (1,2 in your case)

3. Taking Sales and Target data of filtered IDs and SMs.

Check the below script and output and correct me if I am wrong:

T2:
Load * Inline [
ID,SM
1,123
2,234
3,342
];

T3:
Load * Inline [
REPORTMONTH,SM,Target
202101,123,1500
202102,234,2400
202103,341,3500
] where Exists(SM);

Left join (T3)

Load * Inline [
ID,SM
1,123
2,234
3,342
];

Left Join (T3)

T1:
Load * Inline [
ID,YEARMONTH,Sales
1,202101,1000
2,202102,2000
3,202103,3000

];

Drop Table T2;

Exit Script;

Output:

Thanks,

Ashutosh

[MK_QSL]

Try something like below.

T2:
Load * Inline
[
ID,SM
1,123
2,234
3,342
];

Map_ID: Mapping Load SM, ID Resident T2;
Map_SM: Mapping Load ID, SM Resident T2;
Drop Table T2;

T1:
Load SM & ID as %Key, *;
Load
ApplyMap('Map_SM',ID, Null()) as SM
,*
Inline
[
ID,YEARMONTH,Sales
1,202101,1000
2,202102,2000
3,202103,3000
];

T3:
Load SM & ID as %Key, *;
Load
ApplyMap('Map_ID', SM, Null()) as ID
,*
Inline
[
REPORTMONTH,SM,Target
202101,123,1500
202102,234,2400
202103,342,3500
];

T2:
Load Distinct %Key, SM, ID, YEARMONTH as YearMonth, 'Sales' as Flag Resident T1;
Concatenate(T2)
Load Distinct %Key, SM, ID, REPORTMONTH as YearMonth, 'Target' as Flag Resident T3;

Drop Fields ID, SM From T1;
Drop Fields ID, SM From T3;