topic Concatenate tables + Where clause in QlikView

Concatenate tables + Where clause

Fri, 26 Mar 2010 15:20:58 GMT

hi everyone, this time my issue is about concantenate tables depending a where clause

So, I've a table VFACTURA with a date (fec_emis).

The second part, the one i want to concatenate, have the same fields BUT I need to make a condition WHERE the fec_emis of the actual table be over the one of the first part.

In order to get that, i created a variable with the max date of the first part, so i can use it in the where clause of the second one:

VFACTURA:
LOAD

:
fec_emis AS fec_emis,

:
FROM VFACTURAlight.QVD (qvd);

SET Mayor_Fecha= max(fec_emis);

CONCATENATE LOAD

fec_emis AS fec_emis,

FROM VFACTURA.QVD (qvd)
WHERE fec_emis > $(Mayor_Fecha);

STORE VFACTURA INTO VFACTURA2.QVD;

At the reload, it only load the first table and the error: Execution of Script failed, Reload old data? is given.

What am I doing wrong?? Can u recommend me a way to achieve my goal?

Thxs in advance!

Concatenate tables + Where clause

justinasp — Fri, 26 Mar 2010 15:34:50 GMT

I guess it is because max() function requires the group by clause.

Concatenate tables + Where clause

Fri, 26 Mar 2010 15:44:00 GMT

I tried with the GROUP BY enterprise, like:



VFACTURA:
LOAD
 fec_emis AS fec_emis,
 CO_ENTERPRISE,
 :
FROM VFACTURAlight.QVD (qvd)
GROUP BY CO_ENTERPRISE;

SET Mayor_Fecha= max(fec_emis);

And i get the error: Aggregation expressions required by GROUP BY clause

Could you be more specific and show me an example...??

Can i group by any field, indistinctly???

Concatenate tables + Where clause

johnw — Fri, 26 Mar 2010 21:13:39 GMT

I think you want this (untested):

VFACTURA:
LOAD
:
fec_emis AS fec_emis,
:
FROM VFACTURAlight.QVD (qvd);

fec_emis:
LOAD fieldvalue('fec_emis',iterno()) as fec_emis
AUTOGENERATE 1
WHILE len(fieldvalue('fec_emis',iterno()))
;
mayor_fecha:
LOAD max(fec_emis) as mayor_fecha
RESIDENT fec_emis
;
SET Mayor_Fecha = peek('mayor_fecha')
;
DROP TABLES
fec_emis
,mayor_fecha
;

CONCATENATE LOAD
:
fec_emis AS fec_emis,
:
FROM VFACTURA.QVD (qvd)
WHERE fec_emis > $(Mayor_Fecha);

STORE VFACTURA INTO VFACTURA2.QVD;

Concatenate tables + Where clause

wizardo — Sun, 28 Mar 2010 19:05:33 GMT

hi,

it works..... but unless there is somthing im missing (very possible) isnt this simpler?:

VFACTURA:
LOAD

:
fec_emis AS fec_emis,

:
FROM VFACTURAlight.QVD (qvd);

mayor_fecha:
LOAD max(fec_emis) as mayor_fecha
RESIDENT fec_emis
;
SET Mayor_Fecha = peek('mayor_fecha')

drop table mayor_fecha;

CONCATENATE LOAD

:

fec_emis AS fec_emis,

:

FROM VFACTURA.QVD (qvd)
WHERE fec_emis > $(Mayor_Fecha);

STORE VFACTURA INTO VFACTURA2.QVD;

Concatenate tables + Where clause

johnw — Mon, 29 Mar 2010 16:57:28 GMT

Yes, that's simpler (assuming you meant RESIDENT VFACTURA). The reason I don't do it that way is performance. With the simpler approach, you do a resident load of the full table. For smaller data sets, that won't be an issue. But for larger data sets, that can take quite a bit of time.

The approach I'm using instead reads in QlikView's internal list of the possible values of the date. Since there are usually a very limited number of dates compared to rows in the source table, that's usually MUCH faster than reading the source table. The downside, obviously, is the extra script complexity. My approach also won't do what you want if you only want dates from ONE table, and not dates of the same name from a different table, unless you can shift your load order around so that you do this while only the dates you want are in memory.

Either way works.