topic Re: Exclude Rows Using Excel Spreedsheet in QlikView

Exclude Rows Using Excel Spreedsheet

kevbrown — Mon, 09 Dec 2013 13:51:02 GMT

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

Re: Exclude Rows Using Excel Spreedsheet

Anonymous — Mon, 09 Dec 2013 13:54:28 GMT

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

Re: Exclude Rows Using Excel Spreedsheet

Mon, 09 Dec 2013 14:04:18 GMT

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,

Re: Exclude Rows Using Excel Spreedsheet

kevbrown — Mon, 09 Dec 2013 14:48:00 GMT

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

Re: Exclude Rows Using Excel Spreedsheet

Anonymous — Mon, 09 Dec 2013 14:51:53 GMT

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

Re: Exclude Rows Using Excel Spreedsheet

Mon, 09 Dec 2013 15:02:09 GMT

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);

Re: Exclude Rows Using Excel Spreedsheet

kevbrown — Mon, 09 Dec 2013 15:58:15 GMT

Joao,

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

Re: Exclude Rows Using Excel Spreedsheet

Mon, 09 Dec 2013 16:13:46 GMT

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