Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

New field - filled in with complete info

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!

15 Replies
Not applicable
Author

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

qlikmsg4u
Specialist
Specialist

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;

Untitled.png

Not applicable
Author

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; 

12.PNG

Not applicable
Author

As per me, no need to use IF condition. Just AppltMap() is sufficient.

ApplyMap('Map',Doc,'UnKnown') as Account_Number1

Regards,

KKR

Not applicable
Author

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 (qvd)
WHERE GJAHR > 2010 AND MANDT = 101;

Kushal_Chawda

You can simply achieved from front end.

see the attached

Not applicable
Author

Hi Kush141087...I cannot see an attachment. And thanks for helping.

Not applicable
Author

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 (qvd)
WHERE GJAHR > 2010 AND MANDT = 101;

Not applicable
Author

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 (qvd) where len(Account_Number)<>0;

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 (qvd)

WHERE GJAHR > 2010 AND MANDT = 101;


Regards,

KKR