Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Creating Synthetic Index from two SQL table columns. . .

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.

Tags (2)
1 Solution

Accepted Solutions
Employee
Employee

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

You shoud use

[strProject]& [System] instead of

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

3 Replies
Employee
Employee

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

You shoud use

[strProject]& [System] instead of

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

mov
Esteemed Contributor III

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

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"

(?)

israrkhan
Valued Contributor II

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

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;

Community Browser