13 Replies Latest reply: May 8, 2012 10:58 PM by nicksatch RSS

    another tricky part ...

    nicksatch

      Hi fellow Qliksters...

       

      got one tricky table to build...

       

      Column FLAG_REF is what i'm building.

       

      Depending on column FLAG_ACT_FCST, if the value is ACTUAL, it will go M-1, M-2, M-3 in sequence.

      To do this, in the loading script, i'm using the Month value from the first column, and convert it to digit. easy !

       

      BUT, when the column value is FORECAST, i need it to start from F-1, F-2, F-3 in sequence, too.

      and the last count should be at the row of DEC.

      any idea for how the script should look like?

       

      1502.jpg

       

       

      Thanks,

        • Re: another tricky part ...
          Vincent Ardiet

          Hi,

           

          Are you building this expression in your script or in your pivot table ?

          May be if you try to compute this expression : month(min(if(FLAG_ACT_FCST='FORECAST',Month_Year,null())))-1

          Then you can substract this value to your month when your are in a forecast row.

           

          Regards,

          Vincent

            • another tricky part ...
              nicksatch

              Hi Vincent,

               

              i'm writing this in the loading script. Not expression.

               

              so far, i manage to duplicate the table in the script to count the forecast.

              now, i can do for i = 10, bla bla..

              but i need to integrate this in my LOAD. how do i do it just for one column?

            • Re: another tricky part ...
              Sunil Chauhan

              inscript create a row

               

               

              rowno() as row

               

              and then apply

               

              if(FLAG_ACT_FCST='actual','M'&'-'&row,'F'&'-'&(row-count(FLAG_ACT_FCST)))

               

              see the attched file

                • Re: another tricky part ...
                  Sunil Chauhan

                  sorry i forgot to attach

                   

                  see the atttached here

                    • another tricky part ...
                      nicksatch

                      Sunil, for my case, it's not so simple.

                      the starting of F should from 1, not coutinous.

                      and due to limitation, it has to precompute in the LOAD script.

                       

                      Says i have the following...

                       

                      LET vCOUNT = FieldValueCount('test1');  // where test1 is the count() result from the table for rows of FORECAST.

                       

                      Then, i am suppose to use vCOUNT in another load.

                       

                      LOAD

                      'F-'& vCount  // if i do this, vcount is hard coded, how can i do vcount - 1 for each row?

                      FRom...

                        • another tricky part ...
                          Sunil Chauhan

                          better to have sample file if you can????

                          • another tricky part ...
                            nicksatch

                            the thing is, how can i loop a LOAD?

                             

                            i have tried the following, but it wont work

                             

                             

                            LET vCOUNT = FieldValueCount('test1');

                             

                            for each y in $(vCOUNT)

                             

                            LOAD

                            'F-'& $(y)-1

                            FRom...

                             

                            next $(y)


                            • Re: another tricky part ...
                              Vincent Ardiet

                              And with something like this (sorry it's a draft) :

                               

                              test:
                              LOAD Month_Year_FORECAST,
                                   FLAG_ACT_FCST,
                                   QUANTITIY
                              FROM
                              H:\SampleData.xls (biff, embedded labels, table is Feuil1$);

                               

                              test3:
                              mapping LOAD
                              FLAG_ACT_FCST,
                              month(min(if(FLAG_ACT_FCST='FORECAST',Month_Year_FORECAST,null())))-1 as endactual
                              Resident test
                              Group by FLAG_ACT_FCST ;

                               

                              test2:
                              LOAD
                              Month_Year_FORECAST,
                              FLAG_ACT_FCST,
                              QUANTITIY,
                              if(FLAG_ACT_FCST='ACTUAL',
                                     'A'&num(month(Month_Year_FORECAST)),
                                     'F'&(num(month(Month_Year_FORECAST))-num(ApplyMap('test3',FLAG_ACT_FCST)))) as FLAG_REF
                              Resident test ;

                               

                              drop table test ;

                               

                              Regards,

                              Vincent