New Fields almost scripted

[#1]

Hi all,

I am having a bit of difficult with the particulars here, and I think my problem is in exactly where I place my code. I need to create three fields where my scripting is in Green at the bottom below. These are basically a copy/paste of the top three fields created above, but instead of being based on the field called SHKZG it is based on a field called DEB_CRED2. The easiest solution would be that the LIFNR2 field in the Left Join is placed in the code for ReadyTable instead of through a Left Join at all, but I didn't manage that though I am sure I am just writing it wrong somehow. If that is not possible, it probably needs to be in a new table or as a part of the Left Join statement. I have attempted both (below for a new table), but it doesn seem to work. Can anyone crack this cookie for me? Thanks!

SCRIPT:

(The tables where the fields above are loaded FROM are above this, but this section below should be the relevant part)

ReadyTable:

Load *,
If(SHKZG = 'S', tempSpend_NOK * -1, tempSpend_NOK) As #ActualSpend_NOK,
If(SHKZG = 'S', tempTax_NOK * -1, tempTax_NOK) As #ActualTax_NOK,
If(SHKZG = 'S', tempNetSpend_NOK * -1, tempNetSpend_NOK) As #ActualNetSpend_NOK;

Load *,
tempSpend_NOK - tempTax_NOK As tempNetSpend_NOK;

Load *,
#ExchRate * DMBTR As tempSpend_NOK,
#ExchRate * MWSTS As tempTax_NOK;

Load *,
If(UKURS = 0, 1, UKURS) As #ExchRate,
If(POSID<>' ', 1, 0) As _flagWBS,
If(%DocKey = Previous(%DocKey) and BLART <> 'KA', PEEK(DEB_CRED2),SHKZG) as DEB_CRED2
Resident TempHeaderTableSAP;
Store ReadyTable Into C:\QlikView\Fact.qvd (qvd);

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

//ProjectSpend:
//Load *,
//IF(DEB_CRED2 = 'S', tempSpend_NOK * -1, tempSpend_NOK) as #ProjActualSpend_NOK,
//IF(DEB_CRED2 = 'S', tempTax_NOK * -1, tempTax_NOK) as #ProjActualSpend_NOK,
//IF(DEB_CRED2 = 'S', tempNetSpend_NOK * -1, tempNetSpend_NOK) as #ProjActualNetSpend_NOK
//Resident ReadyTable;
//Store ProjectSpend Into C:\QlikView\ProjectSpend.qvd (qvd);

[Gysbert_Wassenaar]

I don't see why LIFNR (or LIFNR2) is needed to calculate those new fields. So you could simply add an additional preceding load to create the new fields by aliasing them from the other three.

I also don't understand the left join. LIFNR already exists in ReadyTable. Just create it directly: if(Len(Trim(LIFNR)),LIFNR) as LIFNR2.

[jonathandienst]

Why can you not do the green lines at the same point in the code as the SHKZG comparisons. I don't see any other dependencies for the DEB_CRED2 calculations. If you preferred, you could place it in a further preceding load statement.

What role does LIFNR2 perform? I don't see any place where its used in the code provided?

But i see no reason why the calculations in green would not work. And your Left Join seems pointless - but I have not seen all the code, of course.

[sasiparupudi1 · Accepted solution]

Load *,

If(SHKZG = 'S', tempSpend_NOK * -1, tempSpend_NOK) As #ActualSpend_NOK,

If(SHKZG = 'S', tempTax_NOK * -1, tempTax_NOK) As #ActualTax_NOK,

If(SHKZG = 'S', tempNetSpend_NOK * -1, tempNetSpend_NOK) As #ActualNetSpend_NOK;

IF(DEB_CRED2 = 'S', tempSpend_NOK * -1, tempSpend_NOK) as #ProjActualSpend_NOK,

IF(DEB_CRED2 = 'S', tempTax_NOK * -1, tempTax_NOK) as #ProjActualSpend_NOK,

IF(DEB_CRED2 = 'S', tempNetSpend_NOK * -1, tempNetSpend_NOK) as #ProjActualNetSpend_NOK

Load *,

tempSpend_NOK - tempTax_NOK As tempNetSpend_NOK;

Load *,

#ExchRate * DMBTR As tempSpend_NOK,

#ExchRate * MWSTS As tempTax_NOK;

Load *,

If(UKURS = 0, 1, UKURS) As #ExchRate,

If(POSID<>' ', 1, 0) As _flagWBS,

If(%DocKey = Previous(%DocKey) and BLART <> 'KA', PEEK(DEB_CRED2),SHKZG) as DEB_CRED2

Resident TempHeaderTableSAP;

Store ReadyTable Into C:\QlikView\Fact.qvd (qvd);

/*

Left Join

Load

%DocKey,

LIFNR as LIFNR2

Resident ReadyTable

Where Len(Trim(LIFNR)) >0;

*/

[#5]

Hi Gisbert. I understand your point about the left join, but I cannot find a formula to do it without using the left join which is unfortunate as your suggestion above would be a much smoother way of doing it. When I do the Left Join with "Where Len(Trim(LIFNR)) >0" then I get a new field with all rows filled in with the correct numbers, but when I try to put it in the code itself (as shown below here), the previously empty rows are now filled with "-" instead of nothing (illustrated below). Could it have something to do with it not having >0?

My code as from your suggestion above:

Load *,
If(UKURS = 0, 1, UKURS) As #ExchRate,
If(POSID<>' ', 1, 0) As _flagWBS,
If(Len(Trim(LIFNR)),LIFNR) as LIFNR2,
If(%DocKey = Previous(%DocKey) and BLART <> 'KA', PEEK(DEB_CRED2),SHKZG) as DEB_CRED2
Resident TempHeaderTableSAP;

What happens:

Row      LIFNR      LIFNR2       LIFNR2 with Left Join

1                            -                 10001

2           10001      10001         10001

3                            -                 10001

[#6]

Hi Sasidhar Parupudi.

Your script above is exactly what I would like to get, however, I constantly get an error message I don't understand when I try to implement it. My error message is about duplicate fields. It says "Field names must be unique within table" and then points to my Load *, If (UKURS = 0,1,UKURS) as #ExchRate, If(POSID<>'',1,0) as _flagWBS, If(%DocKey = Previous(%DocKey) and BLART <> 'KA',PEEK(DEB_CRED2),SHKZG) as DEB_CRED2 Resident TempHeaderTableSAP.

I don't understand why I would get this error message. If I comment away the three If(DEB_CRED2) fields I don't get any error message at all, but when they are included I get that message. And I haven't made any other changes and those three fields add new fields with non-existent names. So why the error message with "field names must be unique within table"??? Have you ever come across this error message occuring before when you don't add a second field with the same name?

ReadyTable:

// Multiplying with -1 for all rows where Debit/Credit is set to 'S'
Load *,
If(SHKZG = 'S', tempSpend_NOK * -1, tempSpend_NOK) As #ActualSpend_NOK,
If(SHKZG = 'S', tempTax_NOK * -1, tempTax_NOK) As #ActualTax_NOK,
If(SHKZG = 'S', tempNetSpend_NOK * -1, tempNetSpend_NOK) As #ActualNetSpend_NOK,
If(DEB_CRED2 = 'S', tempSpend_NOK * -1, tempSpend_NOK) as #ProjActualSpend_NOK,
If(DEB_CRED2 = 'S', tempTax_NOK * -1, tempTax_NOK) as #ProjActualSpend_NOK,
If(DEB_CRED2 = 'S', tempNetSpend_NOK * -1, tempNetSpend_NOK) as #ProjActualNetSpend_NOK;

//Subtracting to get NET values
Load *,
tempSpend_NOK - tempTax_NOK As tempNetSpend_NOK;

//Multiplying spend and taxes by exchange rate
Load *,
#ExchRate * DMBTR As tempSpend_NOK,
#ExchRate * MWSTS As tempTax_NOK;

//Fixing issues where exchangerate = 0
Load *,
If(UKURS = 0, 1, UKURS) As #ExchRate,
// If exchange rate = 0 set value to 1. Not sure if this is correct, we should figure out WHY exchrate is 0 - maybe it's a reason for it?
If(POSID<>' ', 1, 0) As _flagWBS,
//If(Len(Trim(LIFNR)),LIFNR) as LIFNR2,
If(%DocKey = Previous(%DocKey) and BLART <> 'KA', PEEK(DEB_CRED2),SHKZG) as DEB_CRED2
Resident TempHeaderTableSAP;
Store ReadyTable Into C:\QlikView\Fact.qvd (qvd);
Drop table TempHeaderTableSAP;

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

[sasiparupudi1]

the line

If(%DocKey = Previous(%DocKey) and BLART <> 'KA', PEEK(DEB_CRED2),SHKZG) as DEB_CRED2

seems to be the problem.. It looks like you already have a filed DEB_CRED2 in the table..

I suggest you comment that line and see if the  3 DEB_CRED2 lines give you any error

[#8]

The problem was in the code where I named #ProjActualSpend_NOK instead of writing #ProjTaxSpend_NOK for the second field. This fixed it and your original suggestion was perfect. Thanks! 🙂