8 Replies Latest reply: Nov 23, 2015 7:44 AM by Peter Cammaert RSS

    info in field

    Christian Wylezol

      Hi everybody,

       

      can someone tell me how can I write in the script an info of a field in another field, but with a condition?

       

      Example:

      I Need the paydate of linetype 157, in the line with linetype <> 157. But I would load it as separate Field called "CashFlowDate"

       

      oas_details:
      LOAD

      ...

      PAYDATE

      if(LINETYPE<>157,PAYDATE (from the line with linetype = 157 (where doccode, docnum, yr and period are the same like the actual line)), PAYDATE) as CashFlowDate,

      ...

      ;
      SQL SELECT 

      ;
      store oas_details into $(vDATLayerDataQVD)oas_details.qvd;

       

      Does somebody of you have an idea?

       

      Thanks in advance

      Chris

        • Re: info in field
          Marcus Sommer

          You want to access values from other then the current record? Then you could use Peek() or Previous() ?

           

          - Marcus

          • Re: info in field
            Srikanth P

            You can use Peek function in particular case but the usage all depends on your data input , data order and required out put.

             

            Please post some sample data to get the best answer.

            • Re: info in field
              Mark Little

              Hi,

               

              If you know the line that will be stored on them Peek() or previous could give you what you need,

               

              Another options would be to load the value in a variable. (actually will need peek to)

              Table

              Load

                   MAx(Date) as Date

              From 'Your location'

              Where linetype = 157;

               

              LET vTest = PEEK('Date',-1,'Table')

               

              Then use the variable in your if.

               

              Mark

              • Re: info in field
                Peter Cammaert

                If the line types form a group of records with the same key value (for example an InvoiceNo), first load your table without linetype=157. Then JOIN all linetype=157 records to the first table using the key value and call the PAYDATE you join into the first table "CashFlowDate". For example:

                 

                OAS_Details:

                LOAD InvoiceNo // Or whatever key value groups linetypes

                     :

                     PAYDATE

                RESIDENT OriginalSQLData

                WHERE LineType <> 157;

                 

                LEFt JOIN(OAS_Details)

                LOAD InvoiceNo, // Join on this field

                     PAYDATE AS CashFlowDate

                RESIDENT OriginalSQLData

                WHERE LineType = 157;

                 

                Best,

                 

                Peter

                • Re: info in field
                  Christian Wylezol

                  Good morning everybody,

                   

                  thanks for the answers. I dont know what exactly will help.

                  So maybe someone one you can help me a further. I thought the peek-solution could be the one that we need.

                   

                  I made an extract of the database for one number. Every invoice has one table with dochead and one table with all the lines.

                  (the numbers can come again after some years, but Index is I think doccode+docnum)

                  I copied the table to Excel and put in column R the values that I wish to have.

                   

                   

                  I will attached the qvw-file and the qvd-file.

                  Thank you very much in advance.

                  Chris

                    • Re: info in field
                      Peter Cammaert

                      A quick glance at your screenshot shows that the combination of DOCNUM & DOCCODE identifies your two groups of related lines. This leads me to think that the following variation of the original code I posted should do the job. Please try it out.

                       

                      OAS_Details:

                      LOAD DOCNUM, // Or whatever key value groups linetypes

                           DOCCODE,

                           :

                           PAYDATE

                      RESIDENT OriginalSQLData

                      WHERE LineType <> 157;

                       

                      LEFt JOIN(OAS_Details)

                      LOAD DOCNUM, // Join on these fields

                           DOCCODE,

                           PAYDATE AS CashFlowDate

                      RESIDENT OriginalSQLData

                      WHERE LineType = 157;

                        • Re: info in field
                          Christian Wylezol

                          Hi Peter,

                           

                          thank you for your help.

                          I have one issue. the "Resident-Line" does not work.

                          I am taking data our of the database and store them in a qvd-file.

                          Would you please look in my attachment (qvw-file)? What should I do to get it to work?

                           

                          Thanks in advance.

                          Chris

                            • Re: info in field
                              Peter Cammaert

                              You should translate the example to fit your situation.

                               

                              The initial LOAD-SELECT combo for table oasdocline should get an additional WHERE clause in the SELECT statement that says:

                              :

                              WHERE CMPCODE = 'AWD01'

                              //AND DOCCODE = 'RAIFR'

                              AND DOCNUM = '      228613'

                              AND LINETYPE <> 147

                              ;

                               

                              Then LOAD-SELECT your DB Table again. Add a LEFT JOIN(oasdocline) prefix to the preceding LOAD, reduce the number of columns to just :

                               

                              LEFT JOIN(oasdocline)

                              LOAD DOCCODE,

                                   DOCNUM,

                                   PAYDATE AS CashFlowDate

                              :

                               

                              and change the SQL SELECT WHERE clause into something like:

                               

                              :

                              WHERE CMPCODE = 'AWD01'

                              //AND DOCCODE = 'RAIFR'

                              AND DOCNUM = '      228613'

                              AND LINETYPE = 147

                              ;

                               

                              after which you can STORE the internal table into a QVD file.

                               

                              Or you could load the entire table from your DB into memory as a RESIDENT table, and use my previous example from there on. Works considerable faster.

                               

                              Best,

                               

                              Peter