how to join qvd and where to write the GROUP BY?

[#1]

Hi Everyone,

1) I know that when you do join with QV , it automatically take the fields with the same name and do the join by them.

But I want to know how to join the qvd files in the script (like the "ON"in SQL : ... join a1 , a2 ON a1.id=a2.id ), and I also have one inner and one left join ...

Full:

load *
FROM $(QvdPath)Summary.qvd (qvd);
INNER JOIN(Full)
LOAD *
FROM $(QvdPath)Details.qvd (qvd);

LEFT JOIN(Full)
LOAD  "GL Id",
"GL Code" as Account,
"GL Description" as "Account Description" ,
"GL Parent Name" as "Account Parent",
"GL Family Name" as "Account Family"  

2)I also want to add GROUP BY to the Summary.qvd  and Details.qvd - where do I write it ?

thanks,

Moni

[swuehl]

In QV, linking and JOIN of tables are based on same field names as key,

Hence if you want to join QVDs, you need to create tables that have the key fields named identically, and all others differently. You rename fields in the LOAD using AS:

LOAD Customer as KEY,

          City

FROM QVD1.qvd;

// RIGHT, LEFT, INNER, OUTER prefixes to JOIN possible

JOIN

LOAD CustNo AS Customer,

          OrderID,

          Sales

FROM QVD2.qvd;

The GROUP BY comes after the FROM Filename.qvd:

LOAD CustNo,

         Sum(Sales) AS SummedSales

FROM QVD2.qvd

GROUP BY CustNo;

[#3]

thanks swuehl  for your replay.

in the example you wrote , there are not 2 fields with the same name (in the QVD1 you changed the Customer to KEY)- is that by mistake or in purpose ?

thanks

Moni

[ramasaisaksoft · Accepted solution]

Hi Moni,

You already Know by Default QV follows Outer Join.

1) QV never check the data types  of that field and it Checks fields (naming Convention caps & Smalls properly)

2)I hope you already know about Group by clause will come while we use aggregation functions in query ,

load sum(a+b) as c,max(esal) as sal,f1,f2
FROM $(QvdPath)Summary.qvd (qvd) group by f1,f2 ;
INNER JOIN(Full)
LOAD sum(a+b) as c,f1,f2
FROM $(QvdPath)Details.qvd (qvd) group by f1,f2 ;

it is approximately equal to sql query only.

[swuehl]

Right, that's by mistake, not a good example

LOAD Customer as KEY,

          City

FROM QVD1.qvd;