topic Re: ETL with NOT IN in QlikView

ETL with NOT IN

gvasti — Mon, 26 Oct 2015 10:53:23 GMT

Hello guys,

I'm translating many queries and I have some doubts on etl strategy.

There are cases with nested subqueries where I must do the selections on fields on all used tables.

Here's an example:

SELECT

table01.C_PDS,

table01.VOLUME

FROM

table01

WHERE

(

table01.C_CARRIER = @Carrier

AND table01.V_ANNO = year(@StartDate)

AND table01.C_PDS IN

(SELECT

table02.PDS FROM

table02

WHERE

(

table02.DT_INI <= @StartDate

AND table02.DT_FINE >= @EndDate

AND table02.COD_TRASP = @Carrier

AND table02.PDS NOT IN

(SELECT

table03.C_PDR FROM

table03

WHERE

(

table03.V_ANNO = year(@StartDate)

AND table03 = @Carrier

)

)))))

@Carrier, @StartDate and @EndDate are the values that i'll use on the front end with set analysis, lists etc.

Please note that I'm working with large volumes of data and each table corresponds to a QVD file.

What's the best approach that you think?

Thanks in advance

Re: ETL with NOT IN

hic — Tue, 27 Oct 2015 16:25:49 GMT

One way is to use temporary tables and the Exists() function, for example:

tmp3:

Load C_PDR FROM table03.qvd ;

tmp2:

Load PDS FROM table02.qvd

WHERE not Exists(C_PDR,PDS);

RealData:

Load ... FROM table01.qvd

WHERE Exists(PDS,C_PDS);

Drop Tables tmp3, tmp2;

// HIC

Re: ETL with NOT IN

gvasti — Tue, 27 Oct 2015 17:04:17 GMT

Thanks Henric.

I'm not all sure of this solution because there are the selections on table2 and table3 fields.

For example, the NOT IN on table3 probably requires a LEFT JOIN, but my qvd files with 500 milions of record slow very down.

Re: ETL with NOT IN

hic — Tue, 27 Oct 2015 17:27:14 GMT

I don't see a join in your SQL statement...

Re: ETL with NOT IN

gvasti — Wed, 28 Oct 2015 07:56:14 GMT

But with NOT EXISTS how can I make selections on the fields in table02 and table03 ?

Re: ETL with NOT IN

hic — Wed, 28 Oct 2015 13:54:36 GMT

If you want to make selections (i.e. clicking in list boxes in the UI) on the fields in table02 and table03, then you need to load these tables. But then I don't understand your question: Your SQL statement doesn't load these fields.

However, if you just want to set filters during the load sequence, you can do this just by adding conditions to the where clauses in my script.

HIC

Re: ETL with NOT IN

gvasti — Wed, 28 Oct 2015 14:16:08 GMT

Pardon, in my first post I wrote the word "values" in a wrong way:

@Carrier, @StartDate and @EndDate are just the fields that I must use in UI (list box ect).

My problem is: how do I load table01, table02 and table03 ?

How set the filters on UI will be another step.

Re: ETL with NOT IN

hic — Wed, 28 Oct 2015 15:09:14 GMT

I would just load the three tables without any filters, making sure that the keys are OK:

table01:

Load

C_PDS & '|' & V_ANNO as Key,

C_PDS as PDS,

V_ANNO,

C_CARRIER,

VOLUME,

RecNo() as Table01_ID

From table01 ;

table02:

Load

PDS,

DT_INI,

DT_FINE,

COD_TRASP,

RecNo() as Table02_ID

From table02 ;

table03:

Load

C_PDR & '|' & V_ANNO as Key,

RecNo() as Table03_ID

From table03 ;

I assume that the keys are PDS and V_ANNO.

Then you can take care of filtering in the UI.

HIC

Re: ETL with NOT IN

gvasti — Wed, 28 Oct 2015 15:53:17 GMT

Ok, I will load all the table with the correct keys PDS and C_PDR.

Then, how do I write the sum of VOLUME as expression in a pivot?

For example:

SELECT

table01.C_PDS,

sum(table01.VOLUME)

FROM

table01

WHERE

table01.C_CARRIER = @Carrier

AND table01.V_ANNO = year(@StartDate)

AND table01.C_PDS IN

(SELECT

table02.PDS FROM

table02

WHERE

table02.DT_INI <= @StartDate

AND table02.DT_FINE >= @EndDate

AND table02.COD_TRASP = @Carrier

)

group by table01.C_PDS

Re: ETL with NOT IN

hic — Wed, 28 Oct 2015 17:43:26 GMT

You don't sum it in the script. Just use "Sum(VOLUME)" as expression in your pivot table.

HIC

Re: ETL with NOT IN

gvasti — Thu, 29 Oct 2015 07:45:59 GMT

I know that I must use SUM as expression, but I don't know to write the conditions with the IF and/or set analysis.