Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis in Load script itself

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

15 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
hallquist_nate
Partner - Creator III
Partner - Creator III

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

Not applicable
Author

Thank you all for replying

Not applicable
Author

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

hallquist_nate
Partner - Creator III
Partner - Creator III

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.

Not applicable
Author

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

hallquist_nate
Partner - Creator III
Partner - Creator III

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

Not applicable
Author

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

Not applicable
Author

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