Dynamically change of expresions in Bar chart

[Anonymous]

I have a sql statement looking like:

LET vSQL = 'EXEC master.dbo.xp_AnalogWideHistory ' & vBatchStartTime & ', ' & vBatchStopTime & ', ' & 10000 & ', ' & vTag;

Example code:

//SQL EXEC master.dbo.xp_AnalogWideHistory '11-11-2011 07:00:00', '11-11-2011 10:00:00', 10000,'3201L50H007.PV', '3201L50H007_F.PV';

Running this initiates a sql stored procedure that the three parameters vBatchStartTime, vBatchStopTime and vTag.

the result would look like this:

date_time,'3201L50H007.PV', '3201L50H007_F.PV'

11-11-2011 07:00:00, 54,3421,63,9876

11-11-2011 07:00:10, 54,3421,63,9876

11-11-2011 07:00:20, 55,7880,62,1278

11-11-2011 07:00:30, 55,3311,62,2001

...

For the record, it shows the values for selected valve(s) between the start and stop time for a set interval of every 10 second.

My problem is that I want this in a graph.

If I create the expression from the wizard the graph shows.

But I need it to change the expression depending on the vTag content. This can be anything from 1 to typically 10 tags.

Default the expression is SUM([3201L50H007.PV]) and SUM([3201L50H007_F.PV')

How can I set the expression so that it read something like

SUM(vTag)

Is this even possible?

Best regards

Dan

[stevedark · Accepted solution]

This looks to me like you should run your stored procedure into a temporary table, then crosstable load this temporary table, so you have Date, Tag and Value columns, then you can just sum the Value.

The code would be something like:

Temp_Data:

LOAD

     *;

SQL $(vSQL);

Data:

CROSSTABLE (Tag, Value, 1) LOAD

     *

RESIDENT Temp_Data;

DROP TABLE Temp_Data;

Once you have done this Tag is just a dimension and your expression is just Sum(Value) - regardless of the number of tags.

Hope that helps,

- Steve

[Anonymous]

Hi Steve

Thanks for your reply.

I have tried to implement this code. But it cannot load Data.

Even if I change to

Temp_Data:

LOAD

     *;

SQL $(vSQL);

Data:

LOAD

     *

RESIDENT Temp_Data;

Data will not load.

Do not understand why.

Dan

[ToniKautto]

Try quoting the dollar expansion, so that QlikView expands it as a text value

'$(vSQL)'

[Anonymous]

I found what was wrong.

To my surprise I had to promote the tab where I put this code to tab #3, right after the sql query tab.

Thanks for all contributions.

Dan

[stevedark]

Is it possible that the vSQL variable gets cleared between those tabs, or

that a different connection is made?

Anyways, glad it is working now.

- Steve