0 Replies Latest reply: Aug 11, 2011 3:57 PM by Gonzalo Bianchi RSS

    How to exclude data from previously loaded tables...

    Gonzalo Bianchi

      Hi all..

       

      I'm trying to do a special kind of sort for my data...

       

      since the data should be only one row for month, I need to build a table with data from the last 12 months.

       

      To make it clear let see and example..

       

       

      YearMonth
      118
      117
      116
      1011
      115
      114
      113
      112
      111
      108
      99
      97
      9

      10

       

      From this table, I have to reach this:

       

      YearMonth
      118
      117
      116
      115
      114
      113
      112
      111
      108
      1011
      910
      99

       

       

      So how this work? I take the actual year month data if I have it.

      If not, I take the data from the same month on previous years (up to three years).

      if not, I take the last data from a month not used and that will not be used.

       

      To accomplish this, I just go from january to december looking for the best case.

      This works, until december.. if you read the last table, december has to be the record from year 10, month 8, since if you built in order, the other records would already be used.

       

      I have a script that almost do all the job, doing it in this way:

       

      set valido= 'F';

      for i = 1 to 12

          $(valido) = 'F';

      // search for the first record in this month on desc order from periodo (month)

          periodo_temp:

          NoConcatenate

          First 1

          load *

          Resident Tabla_Orig

          Where Mes = $(i)

          Order by Periodo desc;

          LET a = NoOfRows('periodo_temp');

          if ($(a) > 0) then

      //if there is at least one record, append it to the final table

              //QUALIFY *;

              Periodo_final:

              First 1

              load * ,

              (Periodo & Mes) as PeriodoMesUsado

              Resident Tabla_Orig

              Where Mes = $(i)

              Order by Periodo desc;

              //unQUALIFY *;

              $(valido) = 'T';

          ENDIF

          if ($(valido) = 'F') then

      //if there is no record, look for the better record that is not already used. and that will not be used.

              QUALIFY *;

              UNQUALIFY PeriodoMesUsado;

              Periodo_final_1:

              NoConcatenate

              First 1

              load *,

              (Periodo & Mes) as PeriodoMesUsado

              Resident Tabla_Orig

              Where not Exists (Periodo & Mes)

              Order by Periodo desc;

              UNQUALIFY *;

          ENDIF

          DROP Table periodo_temp;

      next

       

      The last if is where I have the problem.. If I use PeriodoMesUsado in the not exist, qlikview says that the field doesn't exists. If I use the concatenate, it will bring just the first record. So How can I get the records from this last table not presents in the final table? I try putting the field into ', and also using the name of the as field. Nothing seems to be working.

       

      Any ideas to share?

       

      Thanks!