Expressions in straight tbl for the sql query

[surajap123]

Hi All,

I wanted to create straight table with dimensions and expressions for the below SQL query.

DECLARE @Date1 datetime

DECLARE @Date2 datetime

SET @Date1 = '06 May 2014'

SET @Date2 = '06 May 2014'

SELECT

Training.Name,  --Dimension

Training.NoOfHours                              AS Hours,

Booking.Units                                       AS Weeks,

(Booking.Units * Training.NoOfHours) AS TotalHours

FROM Booking  LEFT JOIN Training ON Training.Id = Booking.Id

WHERE (Booking.StartDate BETWEEN @Date1 AND @Date2)

AND (Booking.StatusId = 10)

ORDER BY Training.Name

Structure of straight table should be-

Name| Hours| Weeks | TotalHours

Please provide me the expression, i need to write..

[its_anandrjs]

Load your table in the load script

Let vDate1 = '06 May 2014';

Let vDate2 =  '06 May 2014';

Temp:

Load * from Training //Training

Left Join

Load * From Booking //Booking

Final:

Load

Id,

Name,

NoOfHours AS Hours,

Units  AS Weeks,

(Units * NoOfHours) AS TotalHours

From Temp

Where StartDate >= $(vDate1) And StartDate <= $(vDate2) And StatusId = 10  Order By Name;

Note:= But your Date1 and Date2 has same date

[surajap123]

Hi Anand,

Thanks for your reply.

I wanted to create a straight table for the  above sql query. I already have the data model setup in the qlikview document.

[its_anandrjs · Accepted solution]

Update now check i forget to give conditions

But when you already get data from data model why you create a straight table you have to create a table box simple with the fields like

Name| Hours| Weeks | TotalHours

Other wise create a straight table like

Dimension:-

Name

Hours

Weeks

Expression:-

Sum(Units) * Sum(NoOfHours)

OR

Sum(   Units * NoOfHours)

OR

Units * NoOfHours

For your conditions like below script

Sum({<StartDate = {'$(>= $(vDate1)  <= $(vDate2) )'} ,StatusId = {10}>}   Units * NoOfHours)

[surajap123]

Anand..Thanks a lot..