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
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.
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
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.
sub LoadFacts
Load ...
....
end sub ...
70 million rows all qvds together
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 idrop field DummyField;
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 idrop field DummyField;
Thanks Rakesh
Did it work for you? If so, could you please validate the asnwer helped you? This will help others.
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.
Community
QlikView Home Page