Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community
I have below two table (F1 is Fact and R1 is Reference)
I only need to column SID to the table F1 for given CDate and FW. Like
For 1st row in F1 will be like For 2nd row in F1 will be like For 3rd row in F1 will be like
30-Sep-14, CCAR, 101, 1 30-Sep-14, DFAST, 104, 6 31-Dec-14, DFAST, 125, 9
30-Sep-14, CCAR, 101, 2 30-Sep-14, DFAST, 104, 7 31-Dec-14, DFAST, 125, 10
30-Sep-14, CCAR, 101, 3 30-Sep-14, DFAST, 104, 8 31-Dec-14, DFAST, 125, 11
30-Sep-14, CCAR, 101, 4
30-Sep-14, CCAR, 101, 5
And all in one table.
I tried left join but unable to apply two resident tables with filter condition.
F1:
LOAD * INLINE [
CDate, FW, AVal
30-Sep-14, CCAR, 101
30-Sep-14, DFAST, 104
31-Dec-14, DFAST, 125
];
R1:
LOAD * INLINE
[
CDate, FW, Syno, SID
30-Sep-14, CCAR, F B, 1
30-Sep-14, CCAR, F A, 2
30-Sep-14, CCAR, F SA, 3
30-Sep-14, CCAR, B B, 4
30-Sep-14, CCAR, B A, 5
30-Sep-14, DFAST, F B, 6
30-Sep-14, DFAST, F A, 7
30-Sep-14, DFAST, F SA, 8
31-Dec-14, DFAST, B B, 9
31-Dec-14, DFAST, B A, 10
31-Dec-14, DFAST, B SA, 11
];
Two inline tables definition attached for reference
Thanking you
Check this app
F1:
LOAD * INLINE [
CDate, FW, AVal
30-Sep-14, CCAR, 101
30-Sep-14, DFAST, 104
31-Dec-14, DFAST, 125
];
Left Join(F1)
R1:
LOAD * INLINE
[
CDate, FW, Syno, SID
30-Sep-14, CCAR, F B, 1
30-Sep-14, CCAR, F A, 2
30-Sep-14, CCAR, F SA, 3
30-Sep-14, CCAR, B B, 4
30-Sep-14, CCAR, B A, 5
30-Sep-14, DFAST, F B, 6
30-Sep-14, DFAST, F A, 7
30-Sep-14, DFAST, F SA, 8
31-Dec-14, DFAST, B B, 9
31-Dec-14, DFAST, B A, 10
31-Dec-14, DFAST, B SA, 11
];
Hi,
Try this
LOAD * INLINE [
CDate, FW, AVal
30-Sep-14, CCAR, 101
30-Sep-14, DFAST, 104
31-Dec-14, DFAST, 125
];
LEFT JOIN(F1)
LOAD * INLINE
[
CDate, FW, Syno, SID
30-Sep-14, CCAR, F B, 1
30-Sep-14, CCAR, F A, 2
30-Sep-14, CCAR, F SA, 3
30-Sep-14, CCAR, B B, 4
30-Sep-14, CCAR, B A, 5
30-Sep-14, DFAST, F B, 6
30-Sep-14, DFAST, F A, 7
30-Sep-14, DFAST, F SA, 8
31-Dec-14, DFAST, B B, 9
31-Dec-14, DFAST, B A, 10
31-Dec-14, DFAST, B SA, 11
]
WHERE Exists (CDate) ;
F1:
LOAD * INLINE [
]
Left Join(F1)
R1:
LOAD * INLINE [
]
should work
Hi Gabriel
Your anser is helpful but the question is WHERE Exist(CDate) is reqired?
Without this I also achieve result.
Thanking you
Hi Anbu
Your suggestion works greatly, but in addition to this if there are two resident tables. Can we add filter on those?
Thanking you
What is your filter?
If you have two tables to be joined , then you can add flag to differentiate...
Hi Satish,
Use "LEFT JOIN" you will achieve your requirement.
Regards,
Nagarjuna
Manoj
I appreciate.
Your suggestion make sense so I added
Thanks