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 Kumar 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 Kumar 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 Kumar 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

          • another tricky part ...
            nicksatch

            Hi Vincent,

             

            thanks for the suggestion, i'm using it. but because i'm loading multiple excel.

             

            the map works if i only have one file, if i have second file it wont work.

             

            i also try to drop the mapping table but there's an error.

             

            any other suggestion?

             

            Thanks.

            • another tricky part ...
              nicksatch

              i need to find a way to clear all the data in the mapping table. so when the next excel is loaded, the mapping table will be reset. because i'm using Rowno() , if i don't clear/ drop the mapping table, the number just increase continuously...

            • another tricky part ...
              Karthikeyan B

              Is by any chance your excel files have same column names in different tables?

              If it has same column name, it will try to merge the values to match the column values.

               

              Just my thought

              • another tricky part ...
                nicksatch

                all the excel has the same column... it's the file for monthly forecast values....

                i got it working by loading one file at a time...

                i don't know how, but there must be a way we can clear all the table before loading the next file...

                 

                so for the moment.. i can't use for each File in filelist ('..\*.xls')  .... next

                 

                but if anyone know the work around.. please share...