Is null / Catergory

[tan_chungkam]

Hi all,

I have 2 tables from 2 excel files as shown below:

Table 1

MainID	SubID	Type
1	11	Partial
1	12	Partial
1	13	Final
2	21	Partial
2	22	Normal
3	31	Partial
3	32	Partial
3	33	Normal
3	34	Final
4	41	Partial
5	51	Partial
5	52	Normal

Table 2

MainID	SubID
1	11
1	12
1	13
1	14
2	21
2	22
2	23
3	31
3	32
3	33
3	34
4	41
4	42
5	51
5	52
6	61

I have created a new column ('Term') by scripting in T1 qvd.

Now in T2, i would like to link the table 2 to table 1 in order to show the Type and Term for those IDs in table 2.

My question is below:

For those IDs which have null value in Type and Term columns, it should follow as the previous  type and term.

For those new MainID and SubID in table 2 which never appear in table 1, their type and term should be 'TBC'.

Please find screenshots as my outcome.

Thank you.

Scripting for Table1Outcome for Table1Scripting for Table2Outcome for Table2

[OmarBenSalem]

Try sthing as follow:

Table2:
load * Inline [
MainID, SubID
1, 11
1, 12
1, 13
1, 14
2, 21
2, 22
2, 23
3, 31
3, 32
3, 33
3, 34
4, 41
4, 42
5, 51
5, 52
6, 61
];

left Join (Table2)

Table1:
load * Inline [

MainID, SubID, Type
1,11, Partial
1,12, Partial
1,13, Final
2,21, Partial
2,22, Normal
3,31, Partial
3,32, Partial
3,33, Normal
3,34, Final
4,41, Partial
5,51, Partial
5,52, Normal

];

NoConcatenate

Final:
load MainID, SubID, If(len(trim(Type))=0,'TBC',Type) as Type Resident Table2;

drop table Table2;

Result:

[sunny_talwar]

May be try this

Table1:
LOAD * INLINE [
    MainID, SubID, Type
    1, 11, Partial
    1, 12, Partial
    1, 13, Final
    2, 21, Partial
    2, 22, Normal
    3, 31, Partial
    3, 32, Partial
    3, 33, Normal
    3, 34, Final
    4, 41, Partial
    5, 51, Partial
    5, 52, Normal
];

Join (Table1)
LOAD MainID,
	 If(WildMatch(Concat(DISTINCT Type), '*Final*', '*Normal*'), 'P/L', 'B/S') as Term
Resident Table1
Group By MainID;

Table2:
LOAD * INLINE [
    MainID, SubID
    1, 11
    1, 12
    1, 13
    1, 14
    2, 21
    2, 22
    2, 23
    3, 31
    3, 32
    3, 33
    3, 34
    4, 41
    4, 42
    5, 51
    5, 52
    6, 61
];

Join (Table2)
LOAD *
Resident Table1;

DROP Table Table1;

FinalTable2:
NoConcatenate
LOAD MainID,
	 SubID,
	 If(Len(Trim(Type)) = 0, If(MainID = Previous(MainID), Peek('Type'), 'TBC'), Type) as Type,
	 If(Len(Trim(Term)) = 0, If(MainID = Previous(MainID), Peek('Term'), 'TBC'), Term) as Term
Resident Table2
Order By MainID, SubID;

DROP Table Table2;

To get this

[tan_chungkam]

Hi @sunny_talwar,

Thanks for the help.

It works perfectly on these sample data.

However my real data is more complex than these.

Please find below as my real data. It is actually similar to the sample data. The main difference is it is more than 2 tables.

Table 1Table 2Table 3Table 4Outcome

I tried using your scripting on these data. However the quantity of "main order" and "order" increased. It seems wrong. 

[sunny_talwar]

I am not entirely sure I can help by just looking at the script alone. Can you provide 5-10 rows of sample from each of your data source so that I can try to replicate your issue at my end?