topic Re: Set Analysis in Load script itself in QlikView

Set Analysis in Load script itself

Fri, 28 Sep 2012 13:51:26 GMT

Hi guys i want to know if we can do set analysis on the load script itself.

Re: Set Analysis in Load script itself

jonathandienst — Fri, 28 Sep 2012 13:54:10 GMT

I am afraid the simple answer is no.

But you can use SQL where clauses for SQL SELECTs (using SQL commands) and SQL-like where clauses in LOAD statements (using QV commands).

If you need the value(s) for the script logic, you can get them from the loaded tables using Peek().

Regards

Jonathan

Re: Set Analysis in Load script itself

hallquist_nate — Fri, 28 Sep 2012 14:25:52 GMT

I run accross this issue here and there and I build Flags to identify conditions that I need to use in Set Analysis. I then use those flags in formulas in the needed expressions. If you build the flags in Layer 2 of your data model, you could then use where clauses to limit the data load.

Maybe if you let us in on what you need to accomplish, we can help you formulate a solution....

Nate

Re: Set Analysis in Load script itself

Fri, 28 Sep 2012 14:33:14 GMT

Thank you all for replying

Re: Set Analysis in Load script itself

Wed, 22 Jun 2016 14:17:25 GMT

Hi Nate,

could you please tell me, how to write a set expression in load script.. I have an sql query in which a field value prod time has the values from three different Tags. usually, i use the below set expression

Expresion : Sum({$<TagName={fillA}>}[ProdTime])

to get the value corresponding to each Tag.. But now, I want that value to before assigning that value to some variable..

so i want that to be done in load script.

In the above reply, you ar saying about some flags and layer2 of data model. could you please explain me this

Thank you

Ganesh

Re: Set Analysis in Load script itself

hallquist_nate — Wed, 22 Jun 2016 14:36:49 GMT

As far as I know, you can't use Set Expressions in the Load script, because the data is not in memory yet. Here is how the Layers work. Layer 1 QVD's are simply an extract of the raw database table.

//Something like this...

Table1:

Select *

From Data.Table;

Store Table1 into [Layer1\Table1.qvd];

Layer 2 is where you further refine your data, making it more user friendly, formatting it correctly, and other transformation activities. This is where you can build flags to identify data for use in Set Analysis in the presentation layer.

//Something like this...

FactTable2:

Load

Field1 as TransactionType,

Field2 as TransactionID

Date(Field3) as TransactionDate,

If(Field1='Govt', 1, 0) as GovtTransFlag

From Table1;

In Layer 2, I am specifically identifying a Govt Transaction as a 1, for that field. This will allow me to specifically filter for Govt Transactions, using Set analysis in my Presentation Application.

In your case, since you can't use Set Analysis in the script, I would specifically create a field called [FillA Prod Time]. I would do this by writing a bit a script that is something like this...

IF(TagName ='FillA', ProdTime, Null()) as FillA_ProdTime,

This will create a field that populates the Prod Time for every line item that has a FillA Tag name. Then, you can use that field in your presentation application.

Hope this helps.

Re: Set Analysis in Load script itself

Thu, 23 Jun 2016 09:49:14 GMT

Hi Nate, Thank you so much..

it is working for my situation.. its really helpful for me..

is it possible to use, Resident keyword and get the FillA_ProdTime,,i mean to use in layer2.

because i dont want to crearte the QVD file

Thanks

Re: Set Analysis in Load script itself

hallquist_nate — Thu, 23 Jun 2016 14:12:14 GMT

If you have the table in memory, you can do a Resident Load. The scripting would be the same.

Re: Set Analysis in Load script itself

Thu, 23 Jun 2016 14:54:07 GMT

Yeah i did it... and it worked

But, i am unable to figure it out, why I am unable to get the Field value when i use Peak function..

for my surprise, since i have three tags, i used three if conditions like

IF(TagName ='FillA', ProdTime, 0) as FillA_ProdTime,

IF(TagName ='FillB', ProdTime, 0) as FillB_ProdTime,

IF(TagName ='FillC', ProdTime, 0) as FillC_ProdTime

than i used peak function to access the value in a variable for further use

LET vFillA_ProdTime = Peek('FillA_ProdTime',-1,'ProdTable');

let vFillB_ProdTime=Peek('FillB_ProdTime',-1,'ProdTable');

let vFillC_ProdTime=Peek('FillC_ProdTime',-1,'ProdTable');

but, I am getting the value for vFillC_ProdTime but not for other two..

I am unable to figure it out..

but, i am able to see the values for field values when i see in table box .. so there is no problem with the code.. some thing wrong with my peek function

Thank you

Ganesh

Re: Set Analysis in Load script itself

Fri, 24 Jun 2016 16:15:52 GMT

Hi Nate

could you please tell me, is there a way to get the last value for each field in the table..

To be clear, in the above situation, for each tag I will have lot of rows, but I am interested in the last row of each Tagname. Is there a way to get a last record for each value.

usually, we use Peak function to get the Filed value in a Table,we have to give the row number for accessing the value..

In my case, each tag will have more than one value and i have three tagnames, Its impossible to find, which is the last value for each field.. is there a way or any function to do so

please help me wit this

Thank you

Ganesh

Re: Set Analysis in Load script itself

Mon, 27 Jun 2016 08:43:24 GMT

Hi Nate,,

could you please reply me once..

Re: Set Analysis in Load script itself

hallquist_nate — Mon, 27 Jun 2016 12:56:15 GMT

Is there a timestamp that identifies that last row? if there is, you could use that. Since the Peek function isn't working, maybe you could use a GroupBy function to group the Tagnames and then get the max record. I'd have to see some sample data to get more specific. Is there a specific reason why the Peek function isn't working?

Re: Set Analysis in Load script itself

Mon, 27 Jun 2016 13:02:31 GMT

Hi nate, thanks for the reply. I have used Max function, but, I am unable to the date value into a variable, but I am able to get the Date value in text object

please look at the code below

vTagForPO=(FillA, FillB,FillC);

NoConcatenate

GetPOTime:

SELECT TagName as TagsForPOTime,

DateTime as POTime

FROM

History

WHERE

TagName IN$(vTagForPO)

AND DateTime >= '$(vProdStartTime)'

AND DateTime <= '$(vThisExecTime)'

NoConcatenate

FactGetPOChangeTime:

LOAD

TagsForPOTime,

if(TagsForPOTime='Fill A',POTime,0) as Fill A Time,

if(TagsForPOTime='Fill B',POTime,0) as Fill B Time,

if(TagsForPOTime='Fill C',POTime,0) as Fill C Time

Resident GetPOChangeTime;

LET vFillATime=Max([Fill A Time]);

LET vFillBTime=Max([Fill B Time]);

LET vFillCPOTime=Timestamp(Max(Fill C TIme),'YYYY-MM-DD hh:mm:ss');

when i use the this Timestamp(Max(Fill C TIme),'YYYY-MM-DD hh:mm:ss'); expression in a text object, I am getting the exact value

//LET vFillCPOTime1=Time(Max(Time#([BoschCPOTIme],' YYYY-MM-DD hh:mm:ss')),'YYYY-MM-DD hh:mm:ss');

Re: Set Analysis in Load script itself

Tue, 20 Jun 2017 20:29:19 GMT

damn where did nate go

Re: Set Analysis in Load script itself

hallquist_nate — Tue, 20 Jun 2017 20:39:46 GMT

Sorry, been busy with client work and a Job change.

Is this still an open issue?

Nate

Re: Set Analysis in Load script itself

Tue, 20 Jun 2017 20:41:52 GMT

No, I was just looking through old threads trying to solve my issue. I think this thread is resolved though.