15 Replies Latest reply: Jun 20, 2017 4:41 PM by Aaron Wang RSS

    Set Analysis in Load script itself

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

        • Re: Set Analysis in Load script itself
          Jonathan Dienst

          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

          • Re: Set Analysis in Load script itself
            Nate Hallquist

            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

                Thank you all for replying

                • Re: Set Analysis in Load script itself
                  GANESH VARA PRASAD JAKULA

                  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
                      Nate Hallquist

                      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
                          GANESH VARA PRASAD JAKULA

                          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
                              Nate Hallquist

                              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
                                  GANESH VARA PRASAD JAKULA

                                  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
                                    GANESH VARA PRASAD JAKULA

                                    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
                                      GANESH VARA PRASAD JAKULA

                                      Hi Nate,,

                                      could you please reply me once..