topic Re: New Fields almost scripted in QlikView

New Fields almost scripted

Fri, 17 Jul 2015 12:20:03 GMT

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);

Re: New Fields almost scripted

Gysbert_Wassenaar — Fri, 17 Jul 2015 12:41:21 GMT

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.

Re: New Fields almost scripted

jonathandienst — Fri, 17 Jul 2015 12:44:23 GMT

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.

Re: New Fields almost scripted

sasiparupudi1 — Fri, 17 Jul 2015 12:58:10 GMT

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;

Re: New Fields almost scripted

Mon, 20 Jul 2015 06:02:28 GMT

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

Re: New Fields almost scripted

Mon, 20 Jul 2015 06:22:15 GMT

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;

Re: New Fields almost scripted

sasiparupudi1 — Mon, 20 Jul 2015 07:16:33 GMT

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

Re: New Fields almost scripted

Mon, 20 Jul 2015 08:05:04 GMT

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! 🙂