Trying to Concatenate Different WHERE statements from one table

[#1]

Hi everybody,

I have a table which is my one and only source, let's  call it Table1.

I need to load just certain values, and I figured out that, in order to do this, I have to use a concatenate join, as the inclusion criteria are too complex to be summarised in just one WHERE statement. A small sample below

Data	Country	OrderType	Channel	..
..	DE	Return	A
..	DE	Order	Z
..	DE	Return	A
..	NL	Order	Z
..	NL	Order	A
..	NL	Return	Z

If I want, let say, a selection which includes the RETURN orders of DE customers Through Channel A and the Normal Orders of NL customers through Channel Z...

This is impossible to resolve in one single WHERE statement, as some selections are excluding the remaining data.

Thus I was thinking of doing a CONCATENATE LOAD, in this fashion:

Temp:
LOAD *;
SQL SELECT * FROM SERVER.TABLE1;
Temp1:
LOAD *
RESIDENT TEMP
WHERE Country=DE AND OrderType=Return AND Channel=A;
Temp2:
CONCATENATE (Temp1)
LOAD *
RESIDENT TEMP
WHERE Country=NL AND OrderType=Order AND Channel=Z;

For some reasons, when reloaded it does not find the table right when concatenating.

The error goes:

Table not found
concatenate (Temp1)
LOAD *
RESIDENT Temp

It seems to block when it reaches table Temp2.

Can please someone shed some light on this?

Am I asking the moon?

Thank you!

[jonathandienst · Accepted solution]

Hi

Temp1 is getting auto concatenated into Temp, and does not exist when Temp2 is loaded.

All you need is

Temp1:

NoConcatenate

LOAD *.....

Regards

Jonathan

[CELAMBARASAN]

Temp1 is concatenated to Temp because both the table has same number of fields with same filed names.

use NoConcatenate

Why not you can do this in single where statement?

What's wrong if you do in this way?

Temp:
LOAD *;
SQL SELECT * FROM SERVER.TABLE1;


NOConcatenate


Temp1:
LOAD *
RESIDENT TEMP
WHERE (Country=DE AND OrderType=Return AND Channel=A) OR (Country=NL AND OrderType=Order AND Channel=Z);

Drop Table Temp;//If you don't need the Temp table then drop it

Hope it helps

[#4]

actually (I haven't tried this specifically now), you are right.

I remember couple weeks ago, that I had to concatenate a huge table split in two csv files, that by loading them one right after the other, it will concatenate them.

Without concatenate expression.

I 'm lost then what is the meaning of the concatenate load, if qlikview is concatenating by default??

[CELAMBARASAN]

Its a force concatenation.

It will concatenates the two tables if and not if the field names and number of field is not matched.

It will fill the Null for the fields which is not exists in another table.

[#6]

You are right, in this case I could use it.

My exapmle is not portraying the truth 100%.

I'm trying to achieve something like

WHERE ((Not WildMatch(Country,'DE') AND OrderType=Return AND Channel=A) OR (Country=DE AND OrderType=Order AND Channel=Z);

According to the data I receive after the load, the first part of the WHERE statement is excluding the 'DE' records, therefore when in the second part I need a part of these 'DE' records, they are not found.

And I can't reverse the order (I think), because also the second part of the WHERE statement is actually a Not WildMatch

[CELAMBARASAN]

Your expression works in correct way only know?

Your expression works like Country that is not DE with Return Orders in Channel A or Country DE with Orders in Channel Z.

If it is possible to do in Two different where conditions then it can be possible in single where statement.

Where (condition1) or (condition2);

[#8]

Meaning that if I have a table with 1mln rows

and I input a WHERE statement

DE OR NL

QV is going through 1mln lines looking for DE, then starting all over goign through the 1mln lines looking for NL?

[CELAMBARASAN]

Yes.Splitting where statements works in way you explained.

QV is going through 1mln lines looking for DE, then starting all over goign through the 1mln lines looking for NL

If you combine this in a single where statement then it does in single process. No need to check the whole table again for your condition.

[#10]

then something is systematically wrong in my script.

thanks for pointing this out!