How to exclude data from previously loaded tables...

[#1]

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

Year	Month
11	8
11	7
11	6
10	11
11	5
11	4
11	3
11	2
11	1
10	8
9	9
9	7
9	10

From this table, I have to reach this:

Year	Month
11	8
11	7
11	6
11	5
11	4
11	3
11	2
11	1
10	8
10	11
9	10
9	9

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!