IF - Peek: Why am I getting duplicate rows

[Perus_Pena]

Hello,

I have a table with Autonumerate ID as %_KohdeDateID, and Status in field "Huoneiston historia Status".

I'm trying to replace values where status if 'Vapaa' and on the next date it is 'Tarjottu'. I want to overwrite the 'Vapaa' with Tarjottu. And I want to prevent from 'Tarjottu' overwriting all 'Vapaa' Statuses. Only the last one.

This is my script:

AH:
NoConcatenate
Load
[%_KohdeDateID],
"Data pvm",
%_KohdeID,
"Huoneiston historia Status",
IF(Match("Huoneiston historia Status", 'Vapaa') and Match(Peek('Huoneiston historia Status'),'Tarjottu'),'Uus tila',"Huoneiston historia Status") as "New - Huoneiston historia Status",
"Huoneiston tila"
Resident AH_TEMP
Order by %_KohdeDateID desc;

The issue is here, I get duplicate rows where the IF statement is true:

%_KohdeDateID	Data pvm	New - Huoneiston historia Status	Huoneiston historia Status
437	11.8.2019	Vapautumassa	Vapautumassa
438	12.8.2019	Vapautumassa	Vapautumassa
439	13.8.2019	Uus tila	Vapaa
439	13.8.2019	Vapaa	Vapaa
440	14.8.2019	Tarjottu	Tarjottu
441	15.8.2019	Tarjottu	Tarjottu
442	16.8.2019	Tarjottu	Tarjottu
443	17.8.2019	Tarjottu	Tarjottu
444	18.8.2019	Tarjottu	Tarjottu
445	19.8.2019	Tarjottu	Tarjottu
446	20.8.2019	Vapaa	Vapaa
447	21.8.2019	Uus tila	Vapaa
447	21.8.2019	Vapaa	Vapaa
448	22.8.2019	Tarjottu	Tarjottu
449	23.8.2019	Tarjottu	Tarjottu
450	24.8.2019	Tarjottu	Tarjottu

Regards,

Pena

[Anil_Babu_Samineni]

Since you used and operate, If should fulfill both conditions. Perhaps this?

IF(Match("Huoneiston historia Status", 'Vapaa'), If(Match(Peek('Huoneiston historia Status'),'Tarjottu'),'Uus tila',"Huoneiston historia Status")) as "New - Huoneiston historia Status"

[Channa]

try this in preceding load not in resident load

IF( Peek('Huoneiston historia Status')='Tarjottu' and "Huoneiston historia Status"='Vapaa' ,Uus tila,Huoneiston historia Status)

[jonathandienst]

Did you drop table AH_TEMP? If not that would original value still exists.

[JordyWegman]

Hi Perus,

Try this:

Table:
Load * Inline [
%_KohdeDateID,	Data pvm,	New - Huoneiston historia Status,	Huoneiston historia Status
437,	11.8.2019,	Vapautumassa,	Vapautumassa
438,	12.8.2019,	Vapautumassa,	Vapautumassa
439,	13.8.2019,	Uus tila,	Vapaa
439,	13.8.2019,	Vapaa,	Vapaa
440,	14.8.2019,	Tarjottu,	Tarjottu
441,	15.8.2019,	Tarjottu,	Tarjottu
442,	16.8.2019,	Tarjottu,	Tarjottu
443,	17.8.2019,	Tarjottu,	Tarjottu
444,	18.8.2019,	Tarjottu,	Tarjottu
445,	19.8.2019,	Tarjottu,	Tarjottu
446,	20.8.2019,	Vapaa,	Vapaa
447,	21.8.2019,	Uus tila,	Vapaa
447,	21.8.2019,	Vapaa,	Vapaa
448,	22.8.2019,	Tarjottu,	Tarjottu
449,	23.8.2019,	Tarjottu,	Tarjottu
450,	24.8.2019,	Tarjottu,	Tarjottu
];

Load
	%_KohdeDateID,
    [Data pvm],
    [Huoneiston historia Status],
    IF(Peek([Data pvm]) = [Data pvm] and Peek([Huoneiston historia Status]) = [Huoneiston historia Status] and [Huoneiston historia Status] = 'Vapaa', 'Uus tila',[Huoneiston historia Status]) as [New - Huoneiston historia Status]
Resident Table
Order by %_KohdeDateID asc;

Drop table Table;

Jordy

Climber

[Perus_Pena]

Hi Anil,

Thanks for you reply.

With this  IF(Match("Huoneiston historia Status", 'Vapaa'), IF( Match(Peek('Huoneiston historia Status'),'Tarjottu'),'Uus tila',"Huoneiston historia Status")) as "New - Huoneiston historia Status",

I get still duplicate row and now it doesn't copy the value for all rows. But that would be easy to fix. Anyway, for some reason this duplicate row stays and I don't get how that happens.

436	10.8.2019	-	Vapautumassa
437	11.8.2019	-	Vapautumassa
438	12.8.2019	-	Vapautumassa
439	13.8.2019	Uus tila	Vapaa
439	13.8.2019	Vapaa	Vapaa
440	14.8.2019	-	Tarjottu
441	15.8.2019	-	Tarjottu
442	16.8.2019	-	Tarjottu
443	17.8.2019	-	Tarjottu
444	18.8.2019	-	Tarjottu
445	19.8.2019	-	Tarjottu
446	20.8.2019	Vapaa	Vapaa
447	21.8.2019	Uus tila	Vapaa
447	21.8.2019	Vapaa	Vapaa
448	22.8.2019	-	Tarjottu

[Perus_Pena]

Hi Chenna,

Thanks for the reply.

This didn't work. Not sure why but my guess is that peek only works for in memory data sets.

I also tested this in the resident load but didn't do the trick.

-Pena

[Perus_Pena]

Hello Jontydkpi,

Thanks for you reply.

I have drop table after the load. Only one table in the data model viewer.

The duplicate row is also visible in the data viewer if I drop enough Statuses to have only few rows visible in data preview.

-Pena

[Perus_Pena]

Hello Jordy,

Thanks for your reply!

For some reason I still get duplicate rows with this. I simply can't understand how that IF statement can return both True and False.

This is the original data without the resident load (no duplicates). Maybe yours returned the correct as you have the "new column" in the Inline load including. That is not included in the original data but created in the resident load.

434	8.8.2019	Vapautumassa
435	9.8.2019	Vapautumassa
436	10.8.2019	Vapautumassa
437	11.8.2019	Vapautumassa
438	12.8.2019	Vapautumassa
439	13.8.2019	Vapaa
440	14.8.2019	Tarjottu
441	15.8.2019	Tarjottu
442	16.8.2019	Tarjottu
443	17.8.2019	Tarjottu
444	18.8.2019	Tarjottu
445	19.8.2019	Tarjottu
446	20.8.2019	Vapaa
447	21.8.2019	Vapaa
448	22.8.2019	Tarjottu
449	23.8.2019	Tarjottu
450	24.8.2019	Tarjottu
451	25.8.2019	Tarjottu
452	26.8.2019	Siirretään sopimukselle
453	27.8.2019	Sopimuksella

Honestly I'm lost how this load can create duplicate row. It only loads each row once and should have only one possibility to return either false or true corresponding value.

Maybe my best workaround is just to delete the other row in another resident load. I tried to prevent this as in the actual application I'd like to have multiple statuses replaced..

-Pena

[Channa]

It is possible please share some sample data i will find solution i have
similar problem