topic Re: Precalculated field in the same table in QlikView

Precalculated field in the same table

Tue, 17 Nov 2015 20:25:09 GMT

Hi Guys,

We have a problem to solve and I would like to find an elegant and fast solution... but alone is more complicated.

I need a precalculated value to append to the following table:

Tbl_Src:

Id IdObject DateIni DateEnd

The Id is unique

The IdObject is an important field to aggregate the precalc value.

DateIni always will be filled, it is the starting day.

DateEnd can be empty (if it doesn't finish yet).

We need to add the precalculated only for rows with DateEnd=[empty]

SubSet of rows to add the precalculated:

Id_filetered IdObject_filtered DateIni_filtered DateEnd_filtered

We need to count how many Id are related with IdObject_filtered and DateIni between AddMonths(DateIni_filtered,-3) and DateIni_filtered.

By the moment we have set a loop using a Count(Id) with the condition explained above (calculating the number of Id for each row of the subset table), but it is very slow and I am convinced that another way exists to solve the problem.

Thank you in advanced!

Re: Precalculated field in the same table

anlonghi2 — Tue, 17 Nov 2015 21:08:15 GMT

Hi Raul,

please look at attached qlik doc.

To test the approach I used the data stored in the attached excel file.

let me know if I understand correctly your needs.

Best regards

Andrea

Re: Precalculated field in the same table

Wed, 18 Nov 2015 07:20:36 GMT

Thank you Andrea for you fast answer.

It is not exactly the number I needed, let me clarify a little bit:

Using your script we have the same Count for each Id_Object, but we need a differend value for each Id | Id_Object because it depends on the DateIni of each Id | Id_Object.

For exemple, using your Excel file:

3	13/11/2015		C	70000	100
Id	DateIni	DateEnd	Id_Object	Target	Actual
1	13/11/2015	15/11/2015	A	70000	200
15	11/10/2015	11/10/2015	A	70000	100
28	11/09/2015	11/09/2015	A	70000	100
2	13/11/2015	15/11/2015	B	70000	100
16	11/10/2015	11/10/2015	B	70000	100
28	11/09/2015	11/09/2015	B	70000	100
17	11/10/2015	11/10/2015	C	70000	100
28	11/09/2015	11/09/2015	C	70000	100

The value for the Id_Object = C (Id=3)... Should be equal to the number of Ids with IdObject='C' and DateIni between (DateIni-3months) and DateIni of the dates in the row Id=3. This criteria for each row with DateEnd=''. So the calculated value for each row with DateEnd='' can be different.

Thank you again,

Raul

Re: Precalculated field in the same table

anlonghi2 — Wed, 18 Nov 2015 08:25:06 GMT

Hi Raul,

could you share sample data and expected results ?

Regards

Andrea

Re: Precalculated field in the same table

Wed, 18 Nov 2015 09:17:19 GMT

Hi Andrea,

Sure!

For example, here the empty value should be 7 (because it is the same Id_Object and DateIni is between 28/05/2015 and 3 months before):

Please find attached a qvd with all the set of data needed.

Thank you,

Raul

Re: Precalculated field in the same table

anlonghi2 — Wed, 18 Nov 2015 11:29:48 GMT

Hi Raul,

please look at attached new version.

Regards

Andrea

Re: Precalculated field in the same table

Wed, 18 Nov 2015 11:47:19 GMT

Thank you Andrea!

It works and it is a very clean solution.

The Actual field (using the Rand funciont) is not needed, isn't it?

Thank you again,

Raul

Re: Precalculated field in the same table

anlonghi2 — Wed, 18 Nov 2015 13:42:24 GMT

Hi Raul,

thanks for your ack.

Looking better I discovered a calculation error when DateIni of the last record to take in consideration is equal to DateIni of the record with null DateEnd...

So, please, look at attached version that optimize the pivot expression and solve the above issue.

Best regards

Andrea

Re: Precalculated field in the same table

anlonghi2 — Wed, 18 Nov 2015 13:44:52 GMT

yes, you don't need it.

Best regards

Andrea

Re: Precalculated field in the same table

Wed, 18 Nov 2015 16:01:13 GMT

Perfect, now we are not considering cases without EndDate.

Thank you,

Raul