topic Re: Identify matching values in script based on certain criteria in QlikView

Identify matching values in script based on certain criteria

Anonymous — Mon, 08 May 2017 08:55:28 GMT

Hi everybody,

I am currently trying to find out a way to identify matching entries based on certain criteria. This operation needs to be performed in the script.

Please see the example below:

BELNR	AUGBL	SHKZG	DMBTR	DMBTR.absolute	BLDAT	BUDAT	ZFBDT	AUGDT	identify
0002028325	0061226790	S	-981.532	981.532	18.05.2015	18.05.2015	18.05.2015	19.05.2015
0002041310	0061710887	S	-684.226	684.226	16.07.2015	16.07.2015	16.07.2015	20.07.2015
0002022592	0060969774	S	-676.725	676.725	16.04.2015	16.04.2015	16.04.2015	20.04.2015
0002048150	0061971806	S	-509.940	509.940	17.08.2015	17.08.2015	17.08.2015	24.08.2015
0002054686	0062167773	S	-509.873	509.873	16.09.2015	16.09.2015	16.09.2015	17.09.2015
0009001010	0062542784	S	-499.600	499.600	17.09.2015	17.09.2015	17.09.2015	30.10.2015	x
0009001010	2002628310	H	499.600	499.600	17.09.2015	17.09.2015	17.09.2015	17.09.2015	x
0002010016	0060441499	S	-487.373	487.373	16.02.2015	16.02.2015	16.02.2015	23.02.2015
0002021462	0061322831	S	-450.264	450.264	13.04.2015	13.04.2015	13.04.2015	01.06.2015
0013002468	0062972555	S	-386.349	386.349	22.12.2015	23.12.2015	22.12.2015	23.12.2015
0002060987	0062671631	S	-382.279	382.279	16.10.2015	16.10.2015	16.10.2015	17.11.2015
0002070865	0060118587	S	-375.106	375.106	01.12.2015	01.12.2015	01.12.2015	14.01.2016
0002020302	0061322908	S	-357.500	357.500	07.04.2015	07.04.2015	07.04.2015	01.06.2015
0002067379	0062749049	S	-356.369	356.369	16.11.2015	16.11.2015	16.11.2015	26.11.2015
0002015993	0060692060	S	-353.232	353.232	16.03.2015	16.03.2015	16.03.2015	19.03.2015
0009000256	2002448049	H	273.182	273.182	01.02.2015	01.02.2015	01.02.2015	02.02.2015	x
0013002397	0060056626	S	-273.182	273.182	02.01.2014	05.01.2015	02.01.2014	08.01.2015	x
0009000066	60056626	H	272.564	272.564	01.01.2015	01.01.2015	01.01.2015	08.01.2015

The yellow highlighted entries are the matching entries I want to identify. The match is based on the fields DMBTR.absolute and SHKZ. The rule is: Find entries that have identical DMBTR.absolute and the previous entry needs to be a "S" if I am a "H" vs. the previous entry needs to be a "H" if I am a "S". Both entries should be market in a newly created field (identify) with an "x".

Thanks a lot for your help!

Re: Identify matching values in script based on certain criteria

Anil_Babu_Samineni — Mon, 08 May 2017 09:17:18 GMT

Would you provide Sample data set?

Re: Identify matching values in script based on certain criteria

tamilarasu — Mon, 08 May 2017 09:30:44 GMT

Hello,

Have a look at the attached file. Is this your requirement?

Re: Identify matching values in script based on certain criteria

Anonymous — Mon, 08 May 2017 09:30:59 GMT

sure. Here you go

Re: Identify matching values in script based on certain criteria

Anonymous — Mon, 08 May 2017 09:33:36 GMT

perfect. Thank you Tamil!

Re: Identify matching values in script based on certain criteria

tamilarasu — Mon, 08 May 2017 09:38:45 GMT

You are welcome First Name .

Re: Identify matching values in script based on certain criteria

Anonymous — Mon, 08 May 2017 09:44:05 GMT

did a mistake upon my registration - cannot change my personal info anymore.

have a good day!

felipe

Re: Identify matching values in script based on certain criteria

prieper — Mon, 08 May 2017 09:45:52 GMT

would would be the sorting-order?

If already in the above order, do you need to validate with "S" and "H" or would it be enough to check whether the previous value = current value * (-1)?

Peter

Re: Identify matching values in script based on certain criteria

Anonymous — Mon, 08 May 2017 09:49:05 GMT

hi Tamil, could you please let me know why you are dropping Tempidentity field?

Re: Identify matching values in script based on certain criteria

Anonymous — Mon, 08 May 2017 09:49:29 GMT

Hi Peter,

unfortunately not. My data set contains about 100 million entries and I have found examples where there are entries which may not be related to each other based on this assumption. I therefore needed this scripting logic. I knew it was something about "peek" but did not know how to apply it to this example.

regards,

felipe

Re: Identify matching values in script based on certain criteria

tamilarasu — Mon, 08 May 2017 09:50:51 GMT

Felipe,

Just for fun . Below link can help you if you really want to change your name.

How to Change First Name & Last Name in the Account

Have a good day too

Cheers

Re: Identify matching values in script based on certain criteria

tamilarasu — Mon, 08 May 2017 10:12:12 GMT

Hi Kiran,

Since the flag has to be set for both the values I have sorted the data twice. We don't have any function like below() in script level in order to create flag.

In the first load, I have created a TempIdentity flag by sorting the DMBTR.absolute and SHKZG fields in ascending order.

If( (SHKZG = 'S' and Peek('SHKZG') ='H') And (DMBTR.absolute = Peek('DMBTR.absolute')), 'x') as Tempidentity
Resident Data
Order by DMBTR.absolute, SHKZG;

In the next step, I have created the identity flag by sorting the data SHKZG field in descending order. but I want to keep the previously created x in the final result. So finally to get the desired output I have created the identity field which uses the previously created field (Tempidentity)

Result:
Load *,
If( (SHKZG = 'H' and Peek('SHKZG') ='S') And (DMBTR.absolute = Peek('DMBTR.absolute')), 'x', Tempidentity) as identity
Resident IdentityTemp
Order by DMBTR.absolute, SHKZG desc;

DROP Field Tempidentity;

DROP Table Data, IdentityTemp

As we are taking the resident load, all the fields will be available in the resulting table. Since we don't need Tempidentity field anymore, we are dropping it.

Hope this helps you.