topic Re: A formula for cleaning data in a script in Connectivity & Data Prep

A formula for cleaning data in a script

Midchen — Fri, 22 Apr 2022 07:54:36 GMT

Hello,

I'm a beginner, and I don't know how to filter fields in scripts.

The script I loaded was as follows:

Load

Month1,

Month2,

Value

These are the three fields in my script.But actually Value is not the metric I need, and the metric I need needs to be filtered

=Sum({<Month1={">=202203"},[Month2]={"202202"}>
+<Month1={"202202"},[Month2]={"202201"}>
+<Month1={"202201"},[Month2]={"202112"}>
+<Month1={"202112"},[Month2]={"202111"}>
+<Month1={"202111"},[Month2]={"202110"}>
+<Month1={"202110"},[Month2]={"202109"}>
+<Month1={"202109"},[Month2]={"202108"}>
+<Month1={"202108"},[Month2]={"202107"}>
+<Month1={"202107"},[Month2]={"202106"}>
+<Month1={"202106"},[Month2]={"202105"}>
+<Month1={"202105"},[Month2]={"202104"}>}Value)

But I don't think it's necessary to write this function in every diagram. I tried to filter the Value directly in the script, but it was wrong no matter how I wrote it.

How to filter the Value field in the script?

Thank you in advance

Re: A formula for cleaning data in a script

marcus_sommer — Fri, 22 Apr 2022 11:57:02 GMT

Personally I would tend to a logic like the following by adding a more suitable period-field which isn't:

year(date) * 100 + month(date)

else

year(date) * 12 + month(date)

because with it you could always calculate the offset between two periods directly even by the turn of a year.

Afterwards you could apply something like this:

... where Month1 > X and Month2 < Y and Month1 - Month2 = 1;

whereby I think I wouldn't apply a hard filter else using the offset-calculation as flag-field - later used within the set analysis and/or as selection within a list-box.

- Marcus

Re: A formula for cleaning data in a script

Midchen — Sun, 24 Apr 2022 07:51:37 GMT

Thank you for your help. I succeeded.

Where Month1 - Month2 = 1
or Month2=202203 and Month1>202203
or Month1=202201 and Month2=202112;

My results didn't change.Do you have a better expression?

Re: A formula for cleaning data in a script

marcus_sommer — Mon, 25 Apr 2022 06:35:35 GMT

I think the operators and the order between the conditions aren't correct and might look more like:

Where Month1 - Month2 = 1 and
((Month2=202203 and Month1>202203)
or (Month1=202201 and Month2=202112));

Within the UI you may reach just with the mentioned offset-flag, like:

sum({< Flag = {1}>} Value)

and selecting the wanted periods either from Month1 or Month2 because both information will interact with each other. Depending on your final requirements you may need some extra logic like querying for the current period and/or for cases where the flag becomes negative or ...

Additionally you may also considering to extend the data-model with The As-Of Table - Qlik Community - 1466130 to simplify the access on the wanted periods.

- Marcus