8 Replies Latest reply: May 29, 2013 9:59 AM by Friedrich Hofmann RSS

    Set_analysis in script?

    Friedrich Hofmann

      Hi,

       

      I guess I cannot use set_analysis in the script, can I? I haven't yet got it going anyway.

      I have a list of damage events with a date - that might or might not contain today's date, depending on whether there was such an event today or not. I want to display a trend - whether the situation looks better today than on the last day that there was an event. Thus, in case there was an event today, I have to compare the sum we have today (incl. today's event) with that we had last time (not incl. today's event).

      I am doing a RESIDENT LOAD from that table to determine the last date when such an event took place and, if there was one today, I take the date before that from the list.

      The next thing I would like to do is, in case there was an event today and I have thus loaded the date before that, to determine the sum of amounts we had until the last day - not including today's amount.

      The trick is, I have to somehow do this in the script because I want to load all the relevant data in a new table and store it in a result_qvd. But I have failed up to now because it doesn't seem possible to use set_analysis in the script.

      Can anybody help me with an idea how I can load the >>  sum of amounts up to the last event-date before today  << - or simply the amount of today(in case there is one)?

      I hope I have described my problem sufficiently precise, it is quite complicated a matter...

      Thanks!

      Best regards,

       

      DataNibbler

        • Re: Set_analysis in script?

          Hi,

           

          In the script you can use SQL Group By, Sum ... does not help?

           

           

          Rebeca

          • Re: Set_analysis in script?
            Yannick Durand

            I'm not sure how is organized you script, but maybe something like this :

             

            set today = today();

             

            main_table:

            LOAD EVENT, DATE

            FORM YOUR SOURCE;

             

            table_today:

            LOAD sum(EVENT) AS EVENT_TODAY

            RESIDENT main_table

            WHERE DATE = $(today);

             

            table_before:

            LOAD sum(EVENT) AS EVENT_BEFORE

            RESIDENT main_table

            WHERE DATE < $(today)

             

             

            you have then your two value

            the thing is I don't know how you want to process them;

            Maybe you can extract those two values in variables in order to compare them ??

              • Re: Set_analysis in script?
                Friedrich Hofmann

                Hi,

                 

                thanks for helping!

                Aggregation using SUM will not help me since I don't want the sum of ALL the events, but only the sum of the events up to the last_one_before_today.

                The trick is, that list does not contain every date, only those with events, so I don't know if the last_date_of_an_event is today or not. So I have to distinguish:

                - In case the last event was today, I have to sum up all the amounts but for the last one (today's amount)

                - In case the last event was before today, I have to sum up all the amounts regardless.

                I can try using just one LOAD statement with that WHERE clause, however.

                That way, I will automatically get the data from the last event before today, regardless whether there was an event today or not.

                In the result_qvd file I store away I can then load two amounts (identical as likely as not), the amount_up_to_today and the amount_before_today. I can also load the date_of_the_last_event_before_today and I can generate today's date, so I would have everything I need.

                That's as far as theory goes ;-) I'll be back to let you know how it compares to reality.

                Thanks a lot!

                Best regards,

                 

                DataNibbler

                 

                P.S.: Okay, I had to replace SET by LET (to evaluate the expression), now it seems to work. I will test by manipulating the base_list, but the script now runs without error, which is a big step.

                 

                P.P.S.: Well, the script runs without an error - but the values I want to calculate are never generated or so it seems - when I load the finished result_qvd (including those fields) into a new document (using the FIRST 1 parameter since I want only one line), there is nothing in those fields.

                 

                My code for generating those values is:

                 

                LEFT JOIN(orig_table)

                LOAD
                     max(Datum_final) as Datum_letzter_Vorfall_vor_heute,
                     SUM(Schadenshöhe) as Schaeden_bis_zuletzt
                RESIDENT Staplerschaeden
                WHERE(Datum_final < $(v_today));

                 

                That should in any case generate the data for the last_event_before_today, shouldn't it? I subsequently load data into a new table for storing it away in a result_qvd:

                 

                Staplerschaeden_Erg:

                FIRST 1

                LOAD

                     Jahresobergrenze,

                     sum(Schadenshöhe) as Schaeden_bisher,

                     Schaeden_bis_zuletzt,

                     Datum_letzter_Vorfall_vor_heute

                Resident Staplerschaeden

                GROUP BY Jahresobergrenze, Schaeden_bis_zuletzt, Datum_letzter_Vorfall_vor_heute;

                STORE...;

                DROP TABLE ...;

                 

                When I go through the script in debugging_mode, I can see that in the step where those calculated values should be generated and joined to the orig_table, 0 records are being retrieved.

                Can you spot any error in that code?

                Thanks a lot!

                  • Re: Set_analysis in script?
                    Yannick Durand

                    Hi Friedrich

                    Maybe I can try to help you, but I forgot all my German since highschool, so it's kind of hard to understand

                    Could you repost it with english name ?

                     

                    I am not sure I understand your FIRST 1 part.

                    I have never seen it. I suppose it loads the first line ??

                    Are you sure you table is sorted correctly before you extract the first line ??

                      • Re: Set_analysis in script?
                        Yannick Durand

                        I think in fact you can't predict wich will be the first line of Staplerschaeden_Erg because there is a group by clause in it

                         

                         

                        Maybe do :

                         


                        Staplerschaeden_Erg0:

                        FIRST 1

                        LOAD

                             Jahresobergrenze,

                             sum(Schadenshöhe) as Schaeden_bisher,

                             Schaeden_bis_zuletzt,

                             Datum_letzter_Vorfall_vor_heute

                        Resident Staplerschaeden

                        GROUP BY Jahresobergrenze, Schaeden_bis_zuletzt, Datum_letzter_Vorfall_vor_heute

                        ORDER BY your_order_criteria ;

                         

                        Staplerschaeden_Erg:

                        FIRST 1

                        NOCONCATENATE

                        LOAD * RESIDENT Staplerschaeden_Erg0;

                         

                        DROP TABLE Staplerschaeden_Erg0;

                         


                          • Re: Set_analysis in script?
                            Friedrich Hofmann

                            Hi yadurand,

                             

                            I have to use this GROUP BY clause because of that sum function I have in that LOAD.

                            I tried the FIRST 1 parameter because I want only one line in that result_table (Staplerschaeden_Erg). So I need to make sure there is the same value in every line which is why I join those calculated values to the original values. Then the sorting order does not matter as I don't extract any of the original values.

                            The german words in my post are only the fieldnames, they don't make any difference.

                            I will try to get rid of the need for a GROUP BY clause in the final LOAD (for the result_qvd).

                            That, however, does not bring me closer to my actual requirement: I want to sum up all the amounts until the last_one_before_today.

                            I will be back tomorrow. Have to be working on something else now.

                            Thanks a lot!

                            Best regards,

                             

                            DataNibbler

                              • Re: Set_analysis in script?
                                Friedrich Hofmann

                                Hi,

                                 

                                I'll try once more to formulate my exact requirement as precisely as possible, I guess I have been a bit ambiguous:

                                Beginning from the end, there should be four fields in my result_qvd:

                                - An upper limit (a static field from the list) -> no problem

                                - The total sum of amounts (up to today) -> no problem

                                - The last_date_before_today from the list (might be several days back)

                                - The sum of amounts up to the last_date_before_today

                                 

                                And here is what I have and how I think I can get there:

                                - I have two LOAD commands from two worksheets (same workbook, identical structure), which are concatenated.

                                - I already have this code following the two LOADs which works fine:

                                JOIN (orig_table)

                                LOAD

                                     IF(max(Datum_final)=TODAY(), max(Datum_final, 2), max(Datum_final)) as Datum_letzter_Vorfall

                                RESIDENT Staplerschaeden;

                                - Now I want to have one more value to join to my orig_table: The amount of values up to the date I have just calculated (last_date_before_today).

                                It would be cool if I could get both values in one RESIDENT LOAD and join them to the original table -> then I could use the FIRST 1 option to make sure I only load one record into my result_qvd.

                                Can anybody help me there?

                                Can I, when I load max(Datum) with a WHERE clause, somehow tell QlikView to return one other value from the same record? In that case, I could just load the sum of amounts from all records where(date<today) and that should be it, no?

                                Thanks a lot!

                                Best regards,

                                 

                                DataNibbler

                                  • Re: Set_analysis in script?
                                    Friedrich Hofmann

                                    I've made it.

                                    I don't know why - and why it didn't work before.

                                    I have now done just what I wanted to do all along:

                                    - Load (RESIDENT) the total sum of all amounts up to before_today as well as the corresp. date

                                    - JOIN those calculated values to the original table.

                                     

                                    Now I can use the FIRST 1 option to load just one record from the original table, at the same time summing up all the amounts (incl. today), I can simply generate today's date and there I have my four core values.

                                     

                                    Thanks to all of you!

                                     

                                    Best regards,

                                     

                                    DataNibbler