Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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.