17 Replies Latest reply: Jan 31, 2011 8:10 AM by Fernando Tonial RSS

    LOOP

    washington alex

      Because this script just a record breaking quantities of parts?

      tmp:

      load * inline[

      DATA, PARCELAS

      '11/10/2011',20

      '21/10/2011',7

      ];

       

      LET X = peek('PARCELAS');

      LET Z = peek('DATA');

      FOR i = 1 to $(x)

      load * inline

      [

      DATA, PARCELAS

      $(z),$(i)

      ];

      next i

       

        • LOOP
          washington alex

          Can anyone help?

          • LOOP
            Marcel Olmo

            What do you mean with break?

            Which is your desired result? Maybe if you are more specific, we can help you easily. ;)

            This is what you get right now. Right?

             

            DATAPARCELAS
            1
            2
            3
            4
            5
            6
            7
            4082720
            408377
              • LOOP
                washington alex

                Exactly.
                This is what I have now.
                You can observe that the value did not break 20.

                My table is comprised of several records with different field plots.

                In my example, I posted only two record, just one more record breaking obeyed.

                  • LOOP
                    Marcel Olmo

                    Sorry Washington, I still don't get you when you say "the value did not break 20" ¿?

                    Could you put an example of your desired result, like I showed you my final table result before?

                    Thank you.

                • LOOP
                  Nick Borunov

                  did you mean this?

                  tmp:
                  load * inline [
                  DATA, PARCELAS
                  '11/10/2011',20
                  '21/10/2011',7
                  ];

                  Counts:
                  LOAD Count(PARCELAS) as Y
                  Resident tmp;

                  LET y = Peek('Y', 0) - 1;
                  DROP Table Counts;

                  for k = 0 to $(y)

                  LET x = peek('PARCELAS', $(k));
                  LET z = peek('DATA', $(k));

                  FOR i = 1 to $(x)

                  load * inline [
                  DATA, PARCELAS
                  $(z),$(i)
                  ];

                  next i

                  NEXT k


                  But be aware about duplication of two first records.

                    • LOOP
                      washington alex

                      Beauty, solved my problem.

                      Thanks.

                      • LOOP

                        Hi,

                        I change the script for not duplicate.

                         


                        Datas:
                        load * inline [
                        DATA, PARCELAS
                        '11/10/2011',20
                        '21/10/2011',7
                        ];


                        FOR k = 0 TO (NoOfRows('Datas')-1)

                        LET data = peek('DATA', $(k));
                        LET parcela = peek('PARCELAS', $(k));

                        FOR i = 1 to ($(parcela)-1)

                        load
                        Date('$(data)') as DATA,
                        '$(i)' as PARCELAS
                        Autogenerate(1);

                        NEXT i

                        NEXT k



                          • LOOP
                            washington alex

                            Fernando,

                            Because in your example the second record did not break like the original?

                              • LOOP

                                Washington,

                                I did a test here and it worked properly.

                                Following is the table with the data.

                                 

                                 

                                DATA PARCELAS count(DATA&PARCELAS)
                                27
                                11/10/201111
                                11/10/201121
                                11/10/201131
                                11/10/201141
                                11/10/201151
                                11/10/201161
                                11/10/201171
                                11/10/201181
                                11/10/201191
                                11/10/2011101
                                11/10/2011111
                                11/10/2011121
                                11/10/2011131
                                11/10/2011141
                                11/10/2011151
                                11/10/2011161
                                11/10/2011171
                                11/10/2011181
                                11/10/2011191
                                11/10/2011201
                                21/10/201111
                                21/10/201121
                                21/10/201131
                                21/10/201141
                                21/10/201151
                                21/10/201161
                                21/10/201171
                                  • LOOP
                                    washington alex

                                    Fernando,

                                    I'm using the example that you have not left over is breaking the second record:

                                     

                                    Datas:
                                    load * inline [
                                    DATA, PARCELAS
                                    '11/10/2011',20
                                    '21/10/2011',7
                                    ];


                                    FOR k = 0 TO (NoOfRows('Datas')-1)

                                    LET data = peek('DATA', $(k));
                                    LET parcela = peek('PARCELAS', $(k));

                                    FOR i = 1 to ($(parcela)-1)

                                    load
                                    Date('$(data)') as DATA,
                                    '$(i)' as PARCELAS
                                    Autogenerate(1);

                                    NEXT i

                                    NEXT k

                                     



                                      • LOOP

                                        OK,

                                        I understand the problem.

                                        change the command autogenerante.

                                         



                                        load
                                        Date('$(data)','DD/MM/YYYY') as DATA,
                                        '$(i)' as PARCELAS
                                        Autogenerate(1);



                                        OR
                                        change the system variables to Portuguese

                                         



                                        SET ThousandSep='.';
                                        SET DecimalSep=',';
                                        SET MoneyThousandSep='.';
                                        SET MoneyDecimalSep=',';
                                        SET MoneyFormat='R$ #.##0,00;-R$ #.##0,00';
                                        SET TimeFormat='hh:mm:ss';
                                        SET DateFormat='DD/MM/YYYY';
                                        SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
                                        SET MonthNames='jan;fev;mar;abr;mai;jun;jul;ago;set;out;nov;dez';
                                        SET DayNames='seg;ter;qua;qui;sex;sáb;dom';



                                          • LOOP
                                            washington alex

                                            Fernando,

                                            Solved the problem.

                                            Now I'm using this script to a QVD.

                                            Only one field that is not working. It is the field 'Valor_Compra'.

                                            If you can help spin grateful again.

                                            Thanks.

                                             

                                             

                                            Datas:

                                            LOAD

                                             

                                             

                                            COD_FORNECEDOR

                                             

                                            ,

                                            Valor_Compra

                                             

                                            ,

                                            Parcelas

                                             

                                            ,

                                            Prazos

                                            FROM

                                            C:\Importa\tabela.qvd(

                                            qvd);

                                            FOR

                                             

                                             

                                            LET

                                             

                                             

                                            LET

                                             

                                             

                                            LET

                                             

                                             

                                            LET

                                             

                                             

                                            LET

                                             

                                             

                                            FOR

                                             

                                             

                                            load

                                             

                                             

                                            Date

                                             

                                            ('$(data)','DD/MM/YYYY')+('$(Prazo)'*'$(i)') as Data,

                                            '$(i)'



                                            as Parcelas,

                                            '$(fornecedor)'



                                            as COD_FORNECEDOR,

                                            '$(V_Compra)'



                                            as Valor_Compra,

                                            '$(Prazo)'



                                            as Prazos

                                            Autogenerate

                                             

                                            (1);

                                            NEXT

                                             

                                             

                                             

                                            i

                                            NEXT

                                             

                                            k i = 1 to ($(parcela)-1) Prazo = peek('Prazos', $(k)); V_Compra = peek('Valor_Compra', $(k)); fornecedor = peek('COD_FORNECEDOR', $(k)); parcela = peek('Parcelas', $(k)); data = peek('Data', $(k)); k = 0 TO (NoOfRows('Datas')-1) date(Data,'DD/MM/YYYY') AS Data,

                                              • LOOP

                                                Washington,

                                                I can't understand the code you put in.

                                                post the QVW fil.

                                                  • LOOP
                                                    washington alex

                                                    Fernando,

                                                    Note that the field 'Valor_compra' is for each record with two values.

                                                    Also, I need the field 'Plots' come with the original, without breaking. So I have two fields 'Plots', breaking and without breaking.

                                                    I think with these settings I finalize the application.

                                                    Thank you.

                                                      • LOOP

                                                        You can try this code.

                                                         


                                                        qualify *;
                                                        unqualify COD_FORNECEDOR;

                                                        Datas:
                                                        LOAD date(Data,'DD/MM/YYYY') AS Data,
                                                        COD_FORNECEDOR,
                                                        //Round( Valor_Compra) as Valor_Compra,
                                                        Valor_Compra,
                                                        Parcelas,
                                                        Prazos

                                                        FROM tabela.qvd(qvd);

                                                        Unqualify *;


                                                        FOR k = 0 TO (NoOfRows('Datas')-1)

                                                        LET data = peek('Datas.Data', $(k));
                                                        LET parcela = peek('Datas.Parcelas', $(k));
                                                        LET fornecedor = peek('COD_FORNECEDOR', $(k));
                                                        LET V_Compra = peek('Datas.Valor_Compra', $(k));
                                                        LET Prazo = peek('Datas.Prazos', $(k));
                                                        LET vValorParcela = Num('$(V_Compra)')/Num('$(parcela)');

                                                        FOR i = 0 to ($(parcela)-1)

                                                        Parcelas:
                                                        LOAD
                                                        Date(Date('$(data)','DD/MM/YYYY')+('$(Prazo)'*'$(i)'),'DD/MM/YYYY') as Data_Vencimento,
                                                        '$(i)'+1 as Parcelas,
                                                        '$(parcela)' as Total_Parcelas,
                                                        '$(fornecedor)' as COD_FORNECEDOR,
                                                        '$(V_Compra)' as Valor_Compra,
                                                        '$(Prazo)' as Prazos,
                                                        '$(vValorParcela)' as Valor_Parcela
                                                        AUTOGENERATE(1);

                                                        NEXT i

                                                        STORE Parcelas INTO TMP_Parcelas_$(fornecedor).qvd;
                                                        DROP TABLE Parcelas;

                                                        NEXT k


                                                        Parcelas:
                                                        LOAD * FROM TMP_Parcelas_*.qvd (qvd);


                                                        • LOOP

                                                          Hi,

                                                          You ca try this, in autogenerate.

                                                           



                                                          Load
                                                          Date('$(data)','DD/MM/YYYY')+('$(Prazo)'*'$(i)') as Data,
                                                          '$(i)' as Parcelas,
                                                          '$(fornecedor)' as COD_FORNECEDOR,
                                                          Round('$(V_Compra)',0,001) as Valor_Compra,
                                                          '$(Prazo)' as Prazos
                                                          Autogenerate(1);