topic Join two qvds in QlikView

Join two qvds

Mon, 08 Feb 2010 18:41:18 GMT

I would like to know what is the best way to join two tables with 5 same Columns and again each table has 3 different columns.The tables examples are attached.Bothe Tables are qvds.

Thanks

Join two qvds

disqr_rm — Mon, 08 Feb 2010 18:48:00 GMT

If your tables are not huge, just join it:

Tab:
LOAD * from QVD1.qvd(qvd);

Left join (Tab)
LOAD * from QVD2.qvd(qvd;

Then it will automatically join these two tables using the common fields.

Other option is to create a concatenated key merging contents of first 5 fields and storing them as %_JoinKey field.Idea would be to have it prepared bedore you save QVD files and save it in the QVD itself.
For concatenation, good practice is to use a delimiter like "/" for merging contents of multiple fields into one. Something like LOAD f1 & '/' & f2 & '/' & f3 as %_JoinKey,.....

Hope this helps you.

Join two qvds

Mon, 08 Feb 2010 19:07:39 GMT

Hi Rakesh

Thanks for your reply

For real my table 1 : has 9 Key columns same as table 2, ---> 17 coluns same as table 2 ,

----> 5 columns different from table 2

and Table 2: has 9 key columns same as table 1, ---> 17 coluns same as table 1 ,

----> 7 columns different from table 1

when I run the following script it goes fine but I cannot close my debug window and I have to kill my application.

Sub LoadFacts // Every new analysisgid will call this subroutine

Tab:

LOAD *
FROM Facts_$(vAnlys_Gid)_Tab1.qvd (qvd);
concatenate(Tab)
Load *
FROM Facts_$(vAnlys_Gid)_Tab2.qvd (qvd);

End sub

Thanks

Join two qvds

disqr_rm — Mon, 08 Feb 2010 22:14:53 GMT

And how much data in tose QDVs you re dealing with?

One of the problem is that you are defining Tab inside Sub. I would rather do it outside before you call sub first time. You can load null() as dummy to initiate Tab. Then inside Sub I would do Concatenate to Tab for both QVD loads.

Join two qvds

Mon, 08 Feb 2010 22:32:32 GMT

Tab:

sub LoadFacts

Load ...

....

end sub ...

70 million rows all qvds together

Thanks

Join two qvds

disqr_rm — Tue, 09 Feb 2010 00:26:31 GMT

What I meant was that you could try something like this:


Sub LoadFact (cnt)
 Concatenate(Fact)
 LOAD $(cnt) as Field1 autogenerate 1;

 Concatenate(Fact)
 LOAD $(cnt) as Field2 autogenerate 1;
End Sub
Fact:
Load null() as DummyField autogenerate 1;
for i = 1 to 100
 Call LoadFact i;
next i
drop field DummyField;

Join two qvds

Tue, 09 Feb 2010 06:18:12 GMT

Thanks Rakesh

Join two qvds

disqr_rm — Tue, 09 Feb 2010 17:22:08 GMT

Did it work for you? If so, could you please validate the asnwer helped you? This will help others.

Join two qvds

Tue, 09 Feb 2010 19:22:55 GMT

I didn't concatenate or Join two tables becuase the two facts have different kind of calendar.So I linked my second table to main Fact table with a common key as you said using f1 & '/' & f2 & '/' & f3 as %_JoinKey.

Hope this helps.

Thanks