topic Re: Creating Synthetic Index from two SQL table columns. . . in QlikView

Creating Synthetic Index from two SQL table columns. . .

Mon, 28 Oct 2013 19:30:58 GMT

I need to create a synthetic key by just joining two key columns of data into one to join to a second table with identical dual key fields.

But i don't need a cartesian product of all strProject + System. . .

This doesn't appear to work. . There must be a trick to the SQL pass through to the SQLServer 2012 db. . .

Without creating a new SQLSERVER download table. . .

LOAD "Book_Period",

strDistrict,

strProject,

Concat([strProject],[System]) as "ProjectID",

System,

Sequ_Nbr,

Product,

BookDate,

Face_Price,

Est_Material,

Est_Labor,

Est_Subcontractor,

Est_Sundry,

Est_Cost,

Est_Margin;

SQL SELECT *

FROM "FPA_Admin".dbo.BOOKINGS

WHERE Book_Period > 201200 AND Sequ_Nbr=0;

tired from fighting crappy system reports from systems with other programmers. . .

thanks in advance.

Re: Creating Synthetic Index from two SQL table columns. . .

Clever_Anjos — Mon, 28 Oct 2013 19:33:57 GMT

You shoud use

[strProject]& [System] instead of

Concat([strProject],[System]) as "ProjectID",

Re: Creating Synthetic Index from two SQL table columns. . .

Anonymous — Mon, 28 Oct 2013 19:36:31 GMT

Thomas,

It's not quite clear what the problem is. May it's just as simple as this:

autonumberhash128([strProject],[System]) as "ProjectID"

or even

[strProject] & [System] as "ProjectID"

(?)

Re: Creating Synthetic Index from two SQL table columns. . .

israrkhan — Mon, 28 Oct 2013 19:41:24 GMT

try like below:

Table:

LOAD "Book_Period",

strDistrict,

strProject,

strProject]&' / '&[System]) as "ProjectID",

System,

Sequ_Nbr,

Product,

BookDate,

Face_Price,

Est_Material,

Est_Labor,

Est_Subcontractor,

Est_Sundry,

Est_Cost,

Est_Margin;

SQL SELECT *

FROM "FPA_Admin".dbo.BOOKINGS

WHERE Book_Period > 201200 AND Sequ_Nbr=0;