Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Help requested with T-SQL script in Qlikview

Generally Qlikview is great at processing scripts copied and pasted directly from T-SQL. So a SELECT * FROM x WHERE .... type script works perfectly fine, even if it is quite complex (lots of inner and outer joins, CASE WHEN statements, etc etc).

However, I've run into a problem where the SQL script relies on a temporary disk table. The following script format is running okay (i.e. Qlikview reports no errors and returns back to the application when it has finished and displays the Select Fields box). But no data is returned.

QlikviewTable:

SQL SELECT * INTO #temp FROM dbo.x
SELECT a, b, SUM(c), COUNT(d) FROM #temp GROUP BY a, b
DROP #temp;

Could someone tell me what I am doing wrong and how I can fix this? In the above simple example, I need the data from SELECT a, b, SUM(c), COUNT(d) FROM #temp GROUP BY a, b to be returned into the table QlikviewTable. Running the script on my SQL Server gives exactly this.

With thanks

James

1 Solution

Accepted Solutions
Not applicable

Help requested with T-SQL script in Qlikview

try this: TableA: SQL SELECT * FROM dbo.x STORE TableA INTO TableA.qvd; NOCONCATENATE QlikviewTable: LOAD a, b, SUM(c), COUNT(d) RESIDENT TableA GROUP BY a, b; DROP TableA; Cheers Naveen

6 Replies
Not applicable

Help requested with T-SQL script in Qlikview

try this: TableA: SQL SELECT * FROM dbo.x STORE TableA INTO TableA.qvd; NOCONCATENATE QlikviewTable: LOAD a, b, SUM(c), COUNT(d) RESIDENT TableA GROUP BY a, b; DROP TableA; Cheers Naveen

miikkaqlick
Contributor II

Help requested with T-SQL script in Qlikview

Hi!

This works for me:


Temp_Table:
SQL SELECT * INTO temp
FROM dbo."Dim_Dates";

QV_table:
Load *;
Select
DateShortInt,
Count(DateShortInt) as No_Days
From
temp
Group By DateShortInt;

SQL Drop table temp;
[\code]

This gives me 1 table: QV_table. Altough this doesn't drop sql-table temp.

Miikka</body>
mov
Esteemed Contributor III

Help requested with T-SQL script in Qlikview

It is simpler than that:


temp:
SQL SELECT a,b,c,d FROM dbo.x;
//
QlikviewTable:
LOAD
a,
b,
SUM(c) as SumC,
COUNT(d) as CountD
RESIDENT temp
GROUP BY a, b;
//
DROP TABLE temp;


Employee
Employee

Help requested with T-SQL script in Qlikview

Would be easy if you encapsulate your T-SDQL code into a stored procedure and then execute with

sql exec yourprocedurename;

Not applicable

Help requested with T-SQL script in Qlikview

Thanks Mikka

Couldn't get this to work but I follow your logic and was able to get it to work by using RESIDENT.

James

Not applicable

Help requested with T-SQL script in Qlikview

Thanks very much to you all for your help.

By using resident I was able to solve my immediate problem.

I'm still not sure this would work if the second chunk of SQL code was particularly complex (because essentially the first bit is SQL, the second is a LOAD statement in a QV script). In this case, I would have to use a stored procedure as Clever Anjos suggested.

A SP would have been the easiest thing to do - if only the IT department allowed me to create my own SPs and Views!! Right now I don't have access to do this, but fortunately, that's going to change soon.

Thanks again

James

Community Browser