topic Re: Get the Original Amount in QlikView

Get the Original Amount

bsbernabe — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Get the Original Amount

jonathandienst — Thu, 10 May 2018 09:34:06 GMT

During data loading or in a front end table?

Re: Get the Original Amount

Anil_Babu_Samineni — Thu, 10 May 2018 10:16:00 GMT

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;

Re: Get the Original Amount

shiveshsingh — Thu, 10 May 2018 10:22:35 GMT

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

load [Document Number], [Serial No.],

if([Serial No.] = Previous([Serial No.]),Previous(Amount),Amount) as Amount

Resident T;

drop table T;

Re: Get the Original Amount

sasiparupudi1 — Thu, 10 May 2018 10:33:38 GMT

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;

Re: Get the Original Amount

jyothish8807 — Thu, 10 May 2018 10:38:21 GMT

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;