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: 
datanibbler
Champion
Champion

Set_analysis in script?

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

8 Replies
Not applicable

Hi,

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

Rebeca

Not applicable

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 ??

datanibbler
Champion
Champion
Author

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!

Not applicable

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 ??

Not applicable

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;


datanibbler
Champion
Champion
Author

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

datanibbler
Champion
Champion
Author

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

datanibbler
Champion
Champion
Author

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