topic Re: Set_analysis in script? in QlikView

Set_analysis in script?

datanibbler — Mon, 27 May 2013 13:40:26 GMT

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?

Mon, 27 May 2013 16:33:19 GMT

Hi,

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

Rebeca

Re: Set_analysis in script?

Mon, 27 May 2013 17:06:31 GMT

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?

datanibbler — Tue, 28 May 2013 07:13:55 GMT

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?

Tue, 28 May 2013 12:43:58 GMT

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?

Tue, 28 May 2013 12:55:45 GMT

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?

datanibbler — Tue, 28 May 2013 14:22:25 GMT

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?

datanibbler — Wed, 29 May 2013 12:47:19 GMT

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?

datanibbler — Wed, 29 May 2013 13:26:33 GMT

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