topic Remove Row from Table in QlikView

Remove Row from Table

Wed, 07 Apr 2010 09:57:36 GMT

Dear All,

During script running I've to remove some rows from a table.

Instead to put the "where" with all the AND, I need to use a different way, 'cause the items I've to remove are a lot.

I found these way looking to some other forum example but I don't know if it's the fastest and the best one.

Just an example how I've tried to do (I've to exclude value u find in INLINE table for field PSTYV)

I've used this

ExcludeIndia:

LOAD * INLINE [

PSTYV

ZFKP

ZHA2

ZJNT

ZLWM

ZMTO

];

MapTable:

MAPPING LOAD

PSTYV,

1 AS DONT_LOAD

RESIDENT ExcludeIndia;

InvoicePosition:

LOAD CHARG,

FKIMG,

KZWI1,

KZWI2,

MATNR,

POSNR,

PSTYV,

FROM

C:\D\PersonalTraining\Qlikview\Test\EsempiDiTest\FATT_POSIZIONI.xlsx

(ooxml, embedded labels, table is Sheet1)

WHERE ApplyMap('MapTable',PSTYV,0) = 0;

It works but I don't know if there are other better solution to achieve the same result.

Thanks

Sergio

Remove Row from Table

Miguel_Angel_Baeyens — Wed, 07 Apr 2010 10:28:07 GMT

Hello Sergio,

I'm actually using expressions like yours. Working with maps and applymap is fine for me and very elegant and easy to trace should you need to change anything in your code.

Regards.

Remove Row from Table

johnw — Thu, 08 Apr 2010 02:06:06 GMT

You shouldn't need the map in this case, which may speed it up slightly:

ExcludeIndia:
LOAD * INLINE [
ExcludedPSTYV
ZFKP
ZHA2
ZJNT
ZLWM
ZMTO
];
InvoicePosition:
LOAD CHARG,
FKIMG,
KZWI1,
KZWI2,
MATNR,
POSNR,
PSTYV,
FROM
C:\D\PersonalTraining\Qlikview\Test\EsempiDiTest\FATT_POSIZIONI.xlsx
(ooxml, embedded labels, table is Sheet1)
WHERE NOT EXISTS(ExcludedPSTYV,PSTYV);

DROP TABLE ExcludeIndia;

Remove Row from Table

Miguel_Angel_Baeyens — Thu, 08 Apr 2010 08:13:45 GMT

Good one John. I'm using that too. Thanks!

Remove Row from Table

johnw — Thu, 08 Apr 2010 17:27:19 GMT

I doubt this would be any better in this particular case, but just in case, let's say you have a list of all PSTYV codes, whatever they are. It might be faster to do this:

ExcludeIndia:
LOAD * INLINE [
ExcludedPSTYV
ZFKP
ZHA2
ZJNT
ZLWM
ZMTO
];
InvoicePosition:
LOAD PSTYV
FROM your source for all PSTYV codes
WHERE NOT EXISTS(ExcludedPSTYV,PSTYV);

DROP TABLE ExcludeIndia;

INNER JOIN (InvoicePosition)
LOAD CHARG,
FKIMG,
KZWI1,
KZWI2,
MATNR,
POSNR,
PSTYV,
FROM
C:\D\PersonalTraining\Qlikview\Test\EsempiDiTest\FATT_POSIZIONI.xlsx
(ooxml, embedded labels, table is Sheet1);

There's no reason for me to think it would be faster. In fact, it looks like it would be slightly slower. But you never know. Maybe an inner join is more efficient than a "not exists()", and would make up for the wasted time doing an extra load. I doubt it, but having alternatives is a good thing.