Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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"
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)
Did you drop table AH_TEMP? If not that would original value still exists.
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
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 |
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
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
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