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

I have tried now but got an error in a completely new section of my script so unsure what is happening. Suddenly it could not find a field in the next (and untouched) section of the script.

TempHeaderTableSAP:

LOAD
   
BELNR & BUKRS & GJAHR as Doc,
   
BELNR,
   
BUKRS,
   
BUZEI,
   
DMBTR,
   
GJAHR,
    KOSTL,
   
KSTAR,
   
ACCOUNT_NUMBER,
    MANDT,
   
MWSTS,
   
POSID,
   
Left(POSID,6) as project_code,
   
PRCTR,
   
SGTXT,
   
SHKZG
FROM (qvd)
WHERE GJAHR > 2010 AND MANDT = 101;

MAP:

Mapping LOAD
Distinct Doc,
ACCOUNT_NUMBER
Resident TempHeaderTableSAP where Len(LIFNR)<>0;


PROJECT:
Load*,
ApplyMap('MAP',%DocKey,'Unknown') as ACCOUNT_NUMBER2
Resident TempHeaderTableSAP;
Store PROJECT Into C:\QlikView\Project.qvd (qvd);

Not applicable
Author

And when I tried to put the Mapping table earlier it said that my Doc field didn't exists, so that's why I created the new table below

Not applicable
Author

In the TempHeaderTableSAP, you have Doc field. Where as in PROJECT table, you are referring %DocKey which is not available in TempHeaderTableSAP. May be this is causing the issue.

Regards,

KKR

Not applicable
Author

Sorry that was my mistake in the copying into here. In mine they are all called %DocKey, I just changed it to Doc here for easier explanation in my original question, but forgot to change all now in my reply. My bad. But will play around a bit more. Thanks for all the help 🙂

Kushal_Chawda

Can you see now?

Create calculated dimension as below

=aggr(MaxString(Account_Number), Doc)

Not applicable
Author

So I finally got a solution to this problem using an earlier script and a variation of the Len shown above. Thanks for all your guys' help.

I wrote the following script:

Left Join
Load
%DocKey,
LIFNR as LIFNR2
Resident ReadyTable
Where Len(Trim(LIFNR)) >0;