Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello There,
I'm trying figure out/think how can i get the amount of same serial no in document no like "IR" if the document no like "BT" is empty amount.
like:
If Document no like "BT" and serial no amount is empty/null get the amount of document no like IR to same serial no.
Sample Data:
Document Number | Serial No. | Amount |
---|---|---|
IR-00011707 | KPG10E014577 | 58,239 |
BT-00000013 | KPG10E014577 | - |
IR-00019695 | KSW10E079565 | 41,811 |
BT-00000014 | KSW10E079565 | - |
Supposedly Output:
Document Number | Serial No. | Amount |
---|---|---|
IR-00011707 | KPG10E014577 | 58,239 |
BT-00000013 | KPG10E014577 | 58,239 |
IR-00019695 | KSW10E079565 | 41,811 |
BT-00000014 | KSW10E079565 | 41,811 |
Thank you so much ahead
Best Regards,
Bing
Try like this:
Order by would be an imp key.
Table1:
LOAD * Inline [
Document Number, Serial No., Amount
IR-00011707, KPG10E014577, 58239
BT-00000013, KPG10E014577, -
IR-00019695, KSW10E079565, 41811
BT-00000014, KSW10E079565, -
];
Table2:
NoConcatenate
LOAD
[Document Number],
[Serial No.],
if([Serial No.]=Previous([Serial No.]) and [Document Number]<> previous([Document Number]),Previous(Amount),Amount) as Amount
resident Table1
order by [Serial No.];
Drop table Table1;
During data loading or in a front end table?
Is this work?
Table:
LOAD * Inline [
Document Number, Serial No., Amount, Exp
IR-00011707, KPG10E014577, 58239, 58239
BT-00000013, KPG10E014577, -, 58239
IR-00019695, KSW10E079565, 41811, 41811
BT-00000014, KSW10E079565, -, 41811
];
Test:
NoConcatenate
LOAD [Document Number],
[Serial No.],
If([Serial No.]=Previous([Serial No.]),Previous(Amount),Amount) as Amount
Resident Table;
Drop Table Table;
Try this while loading
T:LOAD * INLINE [
Document Number, Serial No., Amount
IR-00011707, KPG10E014577, "58,239"
BT-00000013, KPG10E014577, -
IR-00019695, KSW10E079565, "41,811"
BT-00000014, KSW10E079565, -
];
NoConcatenate
F:
load [Document Number], [Serial No.],
if([Serial No.] = Previous([Serial No.]),Previous(Amount),Amount) as Amount
Resident T;
drop table T;
Another Solution
Table:
LOAD * Inline [
Document Number, Serial No., Amount, Exp
IR-00011707, KPG10E014577, 58239, 58239
BT-00000013, KPG10E014577, -, 58239
IR-00019695, KSW10E079565, 41811, 41811
BT-00000014, KSW10E079565, -, 41811
];
Map_Amount:
Mapping LOAD
[Serial No.],
Amount
Resident Table
Where Index(Lower([Document Number]),'ir-')
;
Final:
NoConcatenate Load
[Document Number], [Serial No.],
If(Index(Lower([Document Number]),'bt-'),Applymap('Map_Amount',[Serial No.],0), Amount ) AS Amount
Resident Table
;
Drop Table Table;
Try like this:
Order by would be an imp key.
Table1:
LOAD * Inline [
Document Number, Serial No., Amount
IR-00011707, KPG10E014577, 58239
BT-00000013, KPG10E014577, -
IR-00019695, KSW10E079565, 41811
BT-00000014, KSW10E079565, -
];
Table2:
NoConcatenate
LOAD
[Document Number],
[Serial No.],
if([Serial No.]=Previous([Serial No.]) and [Document Number]<> previous([Document Number]),Previous(Amount),Amount) as Amount
resident Table1
order by [Serial No.];
Drop table Table1;