question about data manipulation

[Anonymous]

Hi,

While loading the data in QV model I need to exclude those items (in my example - brands) which were present in the last year but not present in the current year. Supppose, that there are only two years in my model.

I did the following:

1) uploaded the data from the source

3) stored the table in QVD format

2) defined variables MinYear and MaxYear

Then I need to define a condition: if there is a record for a particular Brand in MinYear but there is no record for the same Brand in MaxYear, then this particular Brand should be excluded from the table.  How can I do it?

brand_data:
LOAD [Brand],
[Year],
[Budget]

FROM
brand_data.xlsx

LET MaxYear=max([Year])

LET MinYear=min([Year])

Thank you in advance,

Larisa Filonova

[maxgro]

load data of current year

load data of previous year where brand already loaded (use exists)

[Anonymous]

Thanks but it doesn't solve the whole task. Note, that there is also a field Month.

Let me explain by giving you an example.

Let current year be 2014.

Brand A is present in Aug 2014. It is not present in any other month of 2014.

Brand A is present in Jan,Feb,Mar 2013. It is not present in any other month of 2013.

In this case no records should be excluded from my source data, because Brand A is present in 2013 and 2014. It is no matter that months are different.

Suppose, that I followed your advice and loaded the data of the current year (2014). The next step you suggest will work incorrectly in this example. QlikView will not load Brand A (2013) where it has already been loaded in 2014, because there is no record for Jan,Feb,Mar in 2014.

Is there any other solution?

[maxgro]

I think it doesn't matter if brand A is in 2014 august or january

If you first load all months of 2014, at the end of this load (or loads) you have the brands of 2014.

Then you start loading 2013 (full, by months, etc...) but only brands already loaded.

Try to post some data, maybe aI'm missing something

[Anonymous]

I've attached my data. I'd be greatfull if you show me which operators I should use in the script to imlement your idea.

[maxgro · Accepted solution]

Source:

LOAD Brand,

     Year,

     Month,

     [Media type],

     [Cost RUB],

     [Cost USD],

     Quantity

FROM

brand_data.xlsx

(ooxml, embedded labels, table is data)

where Year = 2014

;

Concatenate (Source)

LOAD *

FROM

brand_data.xlsx

(ooxml, embedded labels, table is data)

where Year = 2013

and exists(Brand)

;

[Anonymous]

Thanks a lot!