topic From two tables, create a third table. Consoldiate keys and create new column based on a conditional. in QlikView

From two tables, create a third table. Consoldiate keys and create new column based on a conditional.

Wed, 09 Feb 2011 00:13:40 GMT

Hello,

I'm pretty new to QlikView and SQL.
I am loading two tables from two different sources.
For simplicity, say they look like:

Table1:
Load * Inline
[Key, Subkey, A
1, 1.1, a
2, 2.1, b
2, 2.2, c
5, 5.1, d
5, 5.2, e];

Table2:
Load * Inline
[Key, B
1, f
3, g
4, h
5, i];

I want to create a new table, that consolidates all the keys (all 1-5), with a column C such that C = A if the key exists in Table 1; else C = B.

That is, if(Table1.A is a value, Table1.A, Table2.B)

Want:
Load * Inline
[Key, Subkey, C
1, 1.1, a
2, 2.1, b
2, 2.2, c
3, -, g
4, -, h
5, 5.1, d
5, 5.2, e];

Any help would be appreciated, thanks!

From two tables, create a third table. Consoldiate keys and create new column based on a conditional.

mazacini — Wed, 09 Feb 2011 00:35:47 GMT

You can do this by creating a straight table chart.

In dimensions, add Key and Subkey as dimensions

In expressions, insert into definition if (isnull(Subkey),B,A);

insert "C" into label.

Result:

Key	Subkey	C
		-
1	1.1	a
2	2.1	b
2	2.2	c
3	-	g
4	-	h
5	5.1	d
5	5.2	e

I also had no SQL experience starting out about 2 months ago. I have found it challenging but rewarding. I am still no expert, so I hope my solution works out for you.

Joe

From two tables, create a third table. Consoldiate keys and create new column based on a conditional.

johnw — Wed, 09 Feb 2011 01:17:10 GMT

Is A always present in Table1? Is the Key field unique in Table2? Do you only want the "Want" table at the end, and not the other two tables? If so, I think it's as simple as renaming two fields and doing a where not exists(). No need to do any extra work after the fact. Just doing it during the load of these two tables.

Want:
Load
Key
,Subkey
,A as C
Inline
[Key, Subkey, A
1, 1.1, a
2, 2.1, b
2, 2.2, c
5, 5.1, d
5, 5.2, e];

CONCATENATE (Want)
Load
Key
,B as C
Inline
[Key, B
1, f
3, g
4, h
5, i]
WHERE not exists(Key);

From two tables, create a third table. Consoldiate keys and create new column based on a conditional.

Wed, 09 Feb 2011 17:37:55 GMT

Hi John,

Your assumptions are correct.
I've tried to implement your suggestion:

Table1:
LOAD Key, SubKey, A
FROM Table1.QVD;

Table2:
LOAD Key, B
FROM Table2.QVD;

Want:
Load Key, Subkey, A as C
Resident Table1;

CONCATENATE (Want)
Load Key, B as C
Resident Table2
WHERE not exists(Key);

Unfortunately, the "Key"s that exist in Table2 but not Table1 are not being populated in "Want". Any ideas?
Thanks!

From two tables, create a third table. Consoldiate keys and create new column based on a conditional.

johnw — Thu, 10 Feb 2011 02:08:39 GMT

I don't see anything wrong with your script, though I could certainly be missing something. But your script goes to more effort than should be required. Following the pattern I showed above, I'd do this:

Want:
LOAD Key, Subkey, A as C
FROM Table1.QVD;

CONCATENATE (Want)
LOAD Key, B as C
FROM Table2.QVD
WHERE not exists(Key);

I'm not loading from QVDs, but that shouldn't matter. The attached example shows the script I wrote before working. The same idea SHOULD work with the QVDs. If it doesn't, I'm not sure what's wrong. From a performance standpoint, both QVD loads should still be optimized, as both renaming and a single exists() are allowed. I'm not 100% sure a not exists() is allowed, though.

From two tables, create a third table. Consoldiate keys and create new column based on a conditional.

Thu, 10 Feb 2011 20:00:44 GMT

Hm, so the script I wrote in the previous post does not seem to work:

Table1:
LOAD Key, Subkey, A
Inline
[Key, Subkey, A
1, 1.1, a
2, 2.1, b
2, 2.2, c
5, 5.1, d
5, 5.2, e];

Table2:
LOAD Key, B
Inline
[Key, B
1, f
3, g
4, h
5, i];

Want:
Load Key, Subkey, A as C
Resident Table1;

CONCATENATE (Want)
Load Key, B as C
Resident Table2
WHERE not exists(Key);

The reason (other than the fact I don't know what I'm doing) I make it more complex is because they "Key"s are actually named something different in each table. So I use "as" - perhaps this is the culprit?

The following is more accurate as to what I want to do. I assume the WHERE doesn't cause trouble, but who knows...

Table1:
LOAD Key1 as Key, SubKey, A
FROM Table1.QVD
WHERE A > 0;

Table2:
LOAD Key2 as Key, B
FROM Table2.QVD
WHERE B > 0;

Want:
Load Key, Subkey, A as C
Resident Table1;

CONCATENATE (Want)
Load Key, B as C
Resident Table2
WHERE not exists(Key);

Thanks again for all your help!

From two tables, create a third table. Consoldiate keys and create new column based on a conditional.

johnw — Thu, 10 Feb 2011 20:17:32 GMT

Doh! Now I see the problem. Wish I'd seen it sooner. I get bitten by this one now and again.

The problem happens since you load Table2 before you load your Want table. Once you've done that, ALL values of "Key" exist, so the "not exists(Key)" is always false, so you load nothing from Table2.

Needing to rename the key fields as you read them in isn't a problem, but does require a slight syntax change. The below does the trick. Notice the expanded syntax on the exists() statement.

The WHERE conditions on each table won't cause data problems that I can see, but WILL prevent an optimized load, so performance will suffer. I'll set that aside for now, though, as I don't know that you have an actual performance problem, since that wasn't what you were asking about.

Anyway, here's a working new script and attached example:

Want:
LOAD
Key1 as Key
,Subkey
,A as C
INLINE
[Key1, Subkey, A
1, 1.1, a
2, 2.1, b
2, 2.2, c
5, 5.1, d
5, 5.2, e
6, 6.1,
]
WHERE A > '~'
;

CONCATENATE (Want)
LOAD
Key2 as Key
,B as C
INLINE
[Key2, B
1, f
3, g
4, h
5, i
7,
]
WHERE B > '~'
AND NOT exists(Key,Key2)
;

From two tables, create a third table. Consoldiate keys and create new column based on a conditional.

Thu, 10 Feb 2011 21:27:15 GMT

Perfect, thank you!!