topic SQL script help :) in QlikView

SQL script help :)

Mon, 27 Feb 2012 17:12:13 GMT

Guys,

if i have a table as shown below and i want to select :

1) The hour in which the profit is maxmium for each product ID.

2) Then Seelct the data ( selling proce & cost ) "just a single row" which corresponds to that mentioned hour.

How can i do this using the code in the script

Day Product ID Hour profit Selling price Cost

Sun 1 1:00 3 10 7

Sun 1 2:00 5 15 10

Sun 1 3:00 7 27 20

Sun 1 4:00 8 38 30

Sun 1 5:00 16 32 16

Sun 2 1:00 3 10 7

Sun 2 2:00 5 15 10

Sun 2 3:00 7 27 20

Sun 2 4:00 8 38 30

Sun 2 5:00 16 32 16

Sun 3 1:00 3 10 7

Sun 3 2:00 5 15 10

Sun 3 3:00 7 27 20

Sun 3 4:00 8 38 30

Sun 3 5:00 16 32 16

SQL script help :)

rbecher — Mon, 27 Feb 2012 17:26:12 GMT

Hi,

do you mean only the hour or the day and the hour?

- Ralf

SQL script help :)

rbecher — Mon, 27 Feb 2012 17:30:58 GMT

BTW, something like this:

Results:

LOAD Day & ProductID as %key, Day, ProductID, Hour, Profit, SellingPrice, Cost

Where not exists(%key, Day & ProductID);

SELECT Day, ProductID, Hour, Profit, SellingPrice, Cost

ORDER by Day, ProductID, Profit desc;

- Ralf

SQL script help :)

Jason_Michaelides — Mon, 27 Feb 2012 17:31:31 GMT

Assuming your data source is not an external database (if it was I'd probably group in the SQL query), and assuming you just mean hour and not Day and Hour:

Data_temp:

LOAD

Day

,ProductID

,Hour

,profit

,[Selling price]

,Cost

FROM....;

Map_MaxProfit:

MAPPING LOAD DISTINCT

,ProductID

,Max(profit)

RESIDENT Data_temp

GROUP BY ProductID;

Data:

LOAD

Day

,ProductID

,Hour

,profit

,[Selling price]

,Cost

,IF(Profit=ApplyMap('Map_MaxProfit',ProductID),'Yes','No') AS [Max hour?]

RESIDENT Data_temp;

DROP TABLE Data_temp;

Hope this helps,

Jason

PS - not tested!

SQL script help :)

Tue, 28 Feb 2012 12:16:33 GMT

Thanks jason it worked

SQL script help :)

CELAMBARASAN — Tue, 28 Feb 2012 12:21:56 GMT

Hi,

If the solution solves your problem then close this discussion by mark it as answer.It will help others who search this community for the same type of problem.

Thanks,

Celambarasan

SQL script help :)

Jason_Michaelides — Tue, 28 Feb 2012 12:31:08 GMT

No problem. (Although I think you were supposed to mark my answer as correct, not your thanks )

Jason

SQL script help :)

rbecher — Tue, 28 Feb 2012 13:39:11 GMT

Have you tried my solutions? Seems to be much more compact and ressource efficient...

- Ralf