topic Joining Two Resident Tables Created Using CrossTable in QlikView

Joining Two Resident Tables Created Using CrossTable

Thu, 17 Sep 2009 06:02:06 GMT

Hi,

I have two tables I created via my load script using the crosstable statement for both. See below:


CrossQtr:
CROSSTABLE(Measure, QtrValue,5)
LOAD *
RESIDENT TempQtr;
CrossYr:
CROSSTABLE(Measure, YrValue,4)
LOAD *
RESIDENT TempYr;
[/CODE]
Both tables end up having several fields that are identical, except for the last field being QtrValue & YrValue. What I want to do is JOIN both tables into a single table. I used the following code but it ends up not doing anything:
<pre>
Data:
JOIN(CrossQtr) LOAD * RESIDENT CrossYr;
DROP TABLES CrossQtr, CrossYr;

Can anyone tell me what i'm doing wrong?

Joining Two Resident Tables Created Using CrossTable

johnw — Thu, 17 Sep 2009 06:14:16 GMT

Use CONCATENATE instead of JOIN.

Joining Two Resident Tables Created Using CrossTable

Thu, 17 Sep 2009 06:20:52 GMT

Still doesn't work. For some reason it all just gets dropped and I end up with nothing, no tables, no data, nothing...

Joining Two Resident Tables Created Using CrossTable

johnw — Thu, 17 Sep 2009 06:25:46 GMT

Can you post an example using inline loads?

Joining Two Resident Tables Created Using CrossTable

Anonymous — Thu, 17 Sep 2009 06:27:47 GMT

The "Data" table was never created - instead it was CrossQtr, the one you dropped in the next line.

Joining Two Resident Tables Created Using CrossTable

Thu, 17 Sep 2009 06:40:36 GMT

Ok here's the code using INLINE loads:


 TempQtr:
 LOAD * INLINE [
 Quarter, FiscalYear, TotalRev, EBT, Opex
 Q1, 2009, 1000, 200, 300
 Q2, 2009, 3123, 323, 232
 Q3, 2009, 2323, 323, 323
 Q4, 2009, 2322, 323, 332
];

TempYr:
 LOAD * INLINE [
 FiscalYear, TotalRev, EBT, Opex
 2009, 9878, 45645, 4567
 2009, 52562, 3253, 2532
 2009, 254656, 3243, 3235
 2009, 232522, 3253, 3325];

CrossQtr:
 CROSSTABLE(Measure, QtrValue,2)
 LOAD * RESIDENT TempQtr;

CrossYr:
 CROSSTABLE(Measure, YrValue,1)
 LOAD * RESIDENT TempYr;

Finances:
 LOAD * RESIDENT CrossQtr;
 JOIN LOAD * RESIDENT CrossYr;

DROP TABLES TempQtr, TempYr, CrossQtr, CrossYr;

Joining Two Resident Tables Created Using CrossTable

johnw — Thu, 17 Sep 2009 06:51:01 GMT

Michael's right about why your data disappeared. I didn't catch that at first. As for fixing the problem, you need NOCONCATENATE before the first load to Finances, and CONCATENATE instead of JOIN for the second:

Finances:
NOCONCATENATE LOAD * RESIDENT CrossQtr;
CONCATENATE LOAD * RESIDENT CrossYr;

The first tells it to make a NEW table instead of just giving CrossQtr an alias of Finances. The second tells it to add the rows from CrossYr even though the fields don't all match the existing Finances table.

Joining Two Resident Tables Created Using CrossTable

Anonymous — Thu, 17 Sep 2009 06:53:40 GMT

Same thing... See this part:
Finances:
LOAD * RESIDENT CrossQtr;
Obviously the table Finance was not created because it contains the same exactly fields as CrossQtr. The simplies way to fix is to use only
JOIN (CrossQtr) LOAD * RESIDENT CrossYr;
And, do not drop CrossQtr, because tjhis is the one you want to keep.

Joining Two Resident Tables Created Using CrossTable

Anonymous — Thu, 17 Sep 2009 06:56:21 GMT

Forgot NONCONCATENATE - yes, techically it is simpler to add just one key word.

Joining Two Resident Tables Created Using CrossTable

johnw — Thu, 17 Sep 2009 07:04:10 GMT


Michael Solomovich wrote:Forgot NONCONCATENATE - yes, techically it is simpler to add just one key word.

*chuckle* Yeah, but nicer for maintenance if we get rid of some temp tables. I got it down to one temp table like this:

Finances:
CROSSTABLE(Measure, QtrValue,2)
LOAD * INLINE [
Quarter, FiscalYear, TotalRev, EBT, Opex
Q1, 2009, 1000, 200, 300
Q2, 2009, 3123, 323, 232
Q3, 2009, 2323, 323, 323
Q4, 2009, 2322, 323, 332
];
Temp:
CROSSTABLE(Measure, YrValue,1)
LOAD * INLINE [
FiscalYear, TotalRev, EBT, Opex
2009, 9878, 45645, 4567
2009, 52562, 3253, 2532
2009, 254656, 3243, 3235
2009, 232522, 3253, 3325
];
CONCATENATE ([Finances])
LOAD * RESIDENT Temp;
DROP TABLE Temp;

I would have EXPECTED the following to work without any temp tables, but it did not:

Finances:
CROSSTABLE(Measure, QtrValue,2)
LOAD * INLINE [
Quarter, FiscalYear, TotalRev, EBT, Opex
Q1, 2009, 1000, 200, 300
Q2, 2009, 3123, 323, 232
Q3, 2009, 2323, 323, 323
Q4, 2009, 2322, 323, 332
];
CONCATENATE LOAD *;
CROSSTABLE(Measure, YrValue,1)
LOAD * INLINE [
FiscalYear, TotalRev, EBT, Opex
2009, 9878, 45645, 4567
2009, 52562, 3253, 2532
2009, 254656, 3243, 3235
2009, 232522, 3253, 3325
];

Might be a way, though.

Joining Two Resident Tables Created Using CrossTable

Mon, 28 Feb 2011 15:04:40 GMT

Very usefull, Thanks!

Aldo.

Joining Two Resident Tables Created Using CrossTable

pgalvezt — Mon, 28 Feb 2011 19:28:46 GMT

I have a similar case. Can you help me with this issue? The idea is have no synthetic keys.

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/3835.Dash1.rar:550:0]

Joining Two Resident Tables Created Using CrossTable

johnw — Mon, 28 Feb 2011 19:39:40 GMT

I can't unpack RAR files, and don't want to download software from the web onto my work computer in order to do so. Can you post it as a QVW? I believe that's already a compressed format.

Joining Two Resident Tables Created Using CrossTable

pgalvezt — Mon, 28 Feb 2011 19:45:31 GMT

Of course...There Goes Thanks...

Joining Two Resident Tables Created Using CrossTable

johnw — Mon, 28 Feb 2011 20:25:47 GMT

On the surface, it looks like you could simply concatenate your tables together.

DIRECTORY C:\Documents and Settings\pgalvezt\Desktop\COMMAND CENTER\2 UNILEVER\BRAZIL\Proyecto\QVD;

MyData:
LOAD ... FROM FRETES_INBOUND.qvd (QVD);
CONCATENATE (MyData) LOAD ... FROM FATURA_DE_SERVIÇO.qvd (QVD);
CONCATENATE (MyData) LOAD ... FROM FATURAS.qvd (QVD);
CONCATENATE (MyData) LOAD ... FROM FRETES_OUTBOUND.qvd (QVD);

Or you might want to outer join, assuming that the synthetic key really can be considered the key to all of these tables. In that case, it's probably better to have one row for each key instead of four.

MyData:
LOAD ... FROM FRETES_INBOUND.qvd (QVD);
OUTER JOIN (MyData) LOAD ... FROM FATURA_DE_SERVIÇO.qvd (QVD);
OUTER JOIN (MyData) LOAD ... FROM FATURAS.qvd (QVD);
OUTER JOIN (MyData) LOAD ... FROM FRETES_OUTBOUND.qvd (QVD);

As an aside, I would also typically use a calendar table instead of having all of those date-related fields in the main data table(s). I'd just put a date in the main data table, and put the rest of the fields in the calendar table. It should work either way, though.

Joining Two Resident Tables Created Using CrossTable

pgalvezt — Tue, 01 Mar 2011 15:01:15 GMT

Hi John. Please could you send me a example with your data table explanation?

Basically my dashboard works fine. But i would like to see other examples and to have the better options.

I mention John, I'm working on a major project with one of my clients and I am in the final stages of automating their processes. That is, I think an application that automatically downloads files to csv format, and passes it to xls. Currently, I've created the QVD's, to perform incremental and through the server to perform daily recharges. These dashboards will be showing on screens LCDs 42 '. Dashboards really give you a bonus to the business.

Joining Two Resident Tables Created Using CrossTable

johnw — Wed, 02 Mar 2011 01:57:03 GMT

[quote user="Pablo Gálvez T"]Please could you send me a example with your data table explanation?

I'm sorry, but I don't understand what you want. What do you want an example of? What do you want explained?

Joining Two Resident Tables Created Using CrossTable

pgalvezt — Wed, 02 Mar 2011 13:55:54 GMT

Function Concatenate was the solution to the problem. Thanks John. I needed an example of calendars but i think my calendar works fine 🙂