Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am facing a problem with my script regarding invoicing. Each invoice has several lines, but only one of the lines state an Account_Number. It is important to note that the Account_Number is not necessarily on Line 1.
For certain operations I need the Account_Number field the way it is, but for others I need all lines filled in with whatever account number was relevant for that invoice. Basically what I need is a new field (Account_Number2) where all lines are
filled in. Also some indication on where in my script I put this would be helpful. Should it be in the original table Load statement, or should it be a separate Join, etc.? Can anyone help with this? Thanks!
Some more visual explanation. What I need is basically this final column:
Doc Line Account_Number Account_Number2
001 01 00010-123-0006 00010-123-0006
001 02 00010-123-0006
002 01 00010-124-0001
002 02 00010-124-0001
002 03 00010-124-0001 00010-124-0001
002 04 00010-124-0001
Hi Espen,
Hope this will help
Test:
load * Inline [
Doc, Line, Account_Number
001, 01, 00010-123-0006
001, 02,
002, 01,
002, 02,
002, 03, 00010-124-0001
002, 04,
];
Map:
Mapping load Distinct Doc,Account_Number Resident Test where len(Account_Number)<>0;
Test1:
LOAD Doc,Line,Account_Number,If(len(Account_Number)=0,ApplyMap('Map',Doc),Account_Number) as Account_Number1 Resident Test;
drop table Test;
Hi,
Use the script below:
Tab:
LOAD * INLINE [
Doc, Line, Account Number
001, 01, 00010-123-0006
001, 02
002, 01
002, 02, 00010-124-0001
002, 03
002, 04
];
left join
Tab1:
LOAD Distinct
[Account Number] As [New Account Number],
Doc
Resident Tab where len([Account Number])>0;
As per me, no need to use IF condition. Just AppltMap() is sufficient.
ApplyMap('Map',Doc,'UnKnown') as Account_Number1
Regards,
KKR
Hi,
I cannot load this as an inline table as my real table is HUGE. Almost one million line items. However, is your suggestion here that I load a distinct field with a len() expression. Below is my load statement...is there any way I can augment that in a similar way to what you suggest?
LOAD
BELNR & BUKRS & GJAHR as %DocKey,
BELNR,
BUKRS,
BUZEI,
DMBTR,
GJAHR,
KOSTL,
KSTAR,
LIFNR,
ACCOUNT_NUMBER,
…the new field that I need…,
MANDT,
MWSTS,
POSID,
Left(POSID,6) as project_code,
PRCTR,
SGTXT,
SHKZG
FROM
WHERE GJAHR > 2010 AND MANDT = 101;
You can simply achieved from front end.
see the attached
Hi Kush141087...I cannot see an attachment. And thanks for helping.
Thanks @ Kranthikumar M. That looks more or less correct to me, but I think I am writing it a bit wrong. Can you help me out? I am not familiar with this expression, but I think where I am wrong are the three variables in the parenthesis. Please see below.
LOAD
BELNR & BUKRS & GJAHR as Doc,
BELNR,
BUKRS,
BUZEI,
DMBTR,
GJAHR,
KOSTL,
KSTAR,
LIFNR,
ACCOUNT_NUMBER,
ApplyMap('Map',Doc,'Unknown') as Account_Number1
MANDT,
MWSTS,
POSID,
Left(POSID,6) as project_code,
PRCTR,
SGTXT,
SHKZG
FROM
WHERE GJAHR > 2010 AND MANDT = 101;
As QLIKmsg4u suggested, create a mapping table as below
Map:
Mapping load
Distinct DocField //Put the appropriate field here which represent DoC
,Account_Number
FROM
LOAD
BELNR & BUKRS & GJAHR as %DocKey,
BELNR,
BUKRS,
BUZEI,
DMBTR,
GJAHR,
KOSTL,
KSTAR,
LIFNR,
ACCOUNT_NUMBER,
ApplyMap('Map',DocField,'UnKnown') as Account_Number1 //Put the appropriate field here which represent DoC
MANDT,
MWSTS,
POSID,
Left(POSID,6) as project_code,
PRCTR,
SGTXT,
SHKZG
FROM
WHERE GJAHR > 2010 AND MANDT = 101;
Regards,
KKR