7 Replies Latest reply: Jul 20, 2015 4:05 AM by Espen Lutken RSS

    New Fields almost scripted

    Espen Lutken

      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

          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
              Espen Lutken

              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
              Jonathan Dienst

              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
                Sasidhar Parupudi

                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
                    Espen Lutken

                    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;