Get the Original Amount

[bsbernabe]

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

[jonathandienst]

During data loading or in a front end table?

[Anil_Babu_Samineni]

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;

[shiveshsingh]

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;

[sasiparupudi1]

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;

[jyothish8807 · Accepted solution]

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;