Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys i want to know if we can do set analysis on the load script itself.
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
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
Thank you all for replying
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
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.
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
If you have the table in memory, you can do a Resident Load. The scripting would be the same.
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
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