Exclude Rows Using Excel Spreedsheet

[kevbrown]

I'm loading a table into my QVD but want to exclude some of the rows. I have the exclusions in a separate excel spreedsheet, is there an easy way to exclude them? The data I have is –

RFI                    ProcessStatus               Status               Insured               Originator               Type

RFI_14775                  a                              b                    Yes                         MK                       1

RFI_8720                    a                              c                    Yes                         FD                        1

RFI_8722                    a                              c                    Yes                         RT                        1

RFI_15817                  a                              d                    Yes                         JY                         1

RFI_15607                  a                              u                     No                         WE                        1

RFI_9279                    a                              c                     No                         FG                         1

RFI_9291                    a                              k                     Yes                        RT                         1

RFI_9345                    a                              d                     No                         MK                         2

RFI_18172                  a                              w                    Yes                        TH                          2

RFI_9313                    a                              f                      No                         KB                          2

RFI_9312                    a                              f                     Yes                         EW                         2

RFI_9226                    a                              t                     Yes                         WE                         2

RFI_13798                  a                              k                     Yes                         GY                         2

RFI_9256                    a                              k                     Yes                         GH                         2

RFI_9665                    a                              j                      Yes                         FG                         1

RFI_13564                  a                              j                      No                          KO                          3

RFI_13561                  a                              j                      No                          GH                          3

And I want to exclude RFI_8722, RFI_9345 and RFI_13798 for example.

Thanks

Kev

[Anonymous]

Hi,

First load the field from your excel file as a mapping table.

And then when you are loading your main table, use an applymap statement in the where clause..

map: mapping load Field, 1 as flag from xyz.xls;

maintable: load * from abx.xls where Applymap('map', RFI, 0) = 1;

hope that helps.

Regards,

Aadil

[#3]

Did you tried the transformation step at loading?

When you load your xls file, then click next and select Activate transformation Step;

In editor you can select rows, columns, patterns and exclude the from importing without update your spreadsheet

In hope this can help you,

[kevbrown]

I hadn't tried it but it won't help in this situation. The exclusion spreadsheet could change month on month

[Anonymous]

Oh, I guess I miss understood it earlier.

doing the same example as above. with this change.

map: mapping load Field, 0 as flag from xyz.xls;

maintable: load * from abx.xls where Applymap('map', RFI, 1) = 1;

does this help.

Regards,
Aadil

[#6]

If the exclusion patterns are in another spreadsheet, you could try loads it a separate table and remove the exclusions with a left join like this:

MainData:

LOAD

*

FROM

your-spreadsheet.xlsx

(ooxml, no labels, table is Plan1);

Exclusions:

LOAD

*

FROM

exclusions.xlsx

(ooxml, no labels, table is Plan1);

LEFT JOIN (MainData)

LOAD *

RESIDENT Exclusions

WHERE Not Exists(RFI);

[kevbrown]

Joao,

The reload just hangs. We're running Qlikview 9, that shouldn't make a difference should it?

[#8]

Maybe.

If you have to many columns and rows at your spreadsheet you should be specify the key columns to reduce load charge.

If RFI is the link dimension to exclude the data, you can select only it to knows what you have to load.

Such like this

MainData:

LOAD

RFI

FROM

your-spreadsheet.xlsx

(ooxml, no labels, table is Plan1);

Exclusions:

LOAD

RFI

FROM

exclusions.xlsx

(ooxml, no labels, table is Plan1);

RFIToLoad:

LEFT JOIN (MainData)

LOAD RFI

RESIDENT Exclusions

WHERE Not Exists(RFI);

RFISource:

LOAD *

FROM

your-spreadsheet.xlsx

(ooxml, no labels, table is Plan1);

INNER JOIN (RFIToLoad)

   RFI,

   ProcessStatus,

   etc...

RESIDENT RFISource;

The table RFISouce should contanis your data..

If this works for you, do not forget to drop resident tables in memory with the command

DROP TABLE tablename;

Regards