topic Expressions in straight tbl for the sql query in QlikView

Expressions in straight tbl for the sql query

surajap123 — Tue, 06 May 2014 12:58:34 GMT

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..

Re: Expressions for the sql query

its_anandrjs — Tue, 06 May 2014 13:10:30 GMT

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

Re: Expressions for the sql query

surajap123 — Tue, 06 May 2014 13:14:05 GMT

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.

Re: Expressions for the sql query

its_anandrjs — Tue, 06 May 2014 13:26:21 GMT

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)

Sum( Units * NoOfHours)

Units * NoOfHours

For your conditions like below script

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

Re: Expressions in straight tbl for the sql query

surajap123 — Tue, 06 May 2014 15:31:55 GMT

Anand..Thanks a lot..