Use "Set Type" variable in Where Clause

Fri, 26 Aug 2011 15:47:56 GMT

Hi everyone,

I've a set of values from where I would like to filter the load of a table.So far, I wrote each of those, in the where clause.

GANANCIAS_PERDIDAS:

Directory;

LOAD @1 as COD_EMPRESA,

@2 as MES_O,

@3 as ANNO_O,

MAKEDATE( @3, @2) AS FECHA,

YEAR(MAKEDATE( @3, @2)) AS ANNO,

MONTH(MAKEDATE( @3, @2)) AS MES,

@4 as COD,

mid(@4, 1,3) AS COD_CUENTA,

IF (len(@4)>= 5, mid(@4, 1,5)) AS COD_SUBCUENTA,

IF (len(@4)= 7, mid(@4, 1,7)) AS COD_SUBSUBCUENTA,

@5 as MONTO,

@6 as DEPRECIACION_ACUM,

@7 as MONTO_TOTAL

FROM

info62011.txt

(txt, codepage is 1252, no labels, delimiter is ',', msq)

WHERE

// mid(@4, 1,3) EXISTS $(BG);

(mid(@4, 1,3)='301' OR mid(@4, 1,3)='321' OR mid(@4, 1,3)='341' OR mid(@4, 1,3)='381' OR mid(@4, 1,3)='395' OR

mid(@4, 1,3)='501' OR mid(@4, 1,3)='521' OR mid(@4, 1,3)='541' OR mid(@4, 1,3)='581' OR mid(@4, 1,3)='595'

OR mid(@4, 1,3)='60000') AND @1 ='106';

So far, there arent too many values, but I will be. So, what I need, is to use a variable, set type, and use it in thw where clause.

Something like:

SET BG='301;321;341;381;395;501;521;541;581;595;60000';

And in where use something like mid(@4, 1,3) EXISTS $(BG); instead.

Is that posible??

Any ideas?

Thxs in Advance!

Leonor

Use "Set Type" variable in Where Clause

swuehl — Fri, 26 Aug 2011 17:59:32 GMT

Hi Leonor,

what could work is something like

set vTest = (10,20,30);

Product:

LOAD ProductID,

.....

ProductName;

SQL SELECT *

FROM Product where mid(ProductNumber,5,2) in $(vTest);

I said "could work", because above example worked in real life, but the working of the where...in.. clause is depending on the underlying data source driver. For example, I think this will not work if you load in a plain text file (like in your example).

Regards,

Stefan

topic Use "Set Type" variable in Where Clause in QlikView

Use "Set Type" variable in Where Clause

Use "Set Type" variable in Where Clause