Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
Perus_Pena
Contributor III
Contributor III

IF - Peek: Why am I getting duplicate rows

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:

 
%_KohdeDateIDData pvmNew - Huoneiston historia StatusHuoneiston historia Status
43711.8.2019VapautumassaVapautumassa
43812.8.2019VapautumassaVapautumassa
43913.8.2019Uus tilaVapaa
43913.8.2019VapaaVapaa
44014.8.2019TarjottuTarjottu
44115.8.2019TarjottuTarjottu
44216.8.2019TarjottuTarjottu
44317.8.2019TarjottuTarjottu
44418.8.2019TarjottuTarjottu
44519.8.2019TarjottuTarjottu
44620.8.2019VapaaVapaa
44721.8.2019Uus tilaVapaa
44721.8.2019VapaaVapaa
44822.8.2019TarjottuTarjottu
44923.8.2019TarjottuTarjottu
45024.8.2019TarjottuTarjottu

 

Regards,

Pena

Labels (3)
12 Replies
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"

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Channa
Specialist III
Specialist III

 

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)

Channa
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
JordyWegman
Partner - Master
Partner - Master

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

Work smarter, not harder
Perus_Pena
Contributor III
Contributor III
Author

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.

43610.8.2019-Vapautumassa
43711.8.2019-Vapautumassa
43812.8.2019-Vapautumassa
43913.8.2019Uus tilaVapaa
43913.8.2019VapaaVapaa
44014.8.2019-Tarjottu
44115.8.2019-Tarjottu
44216.8.2019-Tarjottu
44317.8.2019-Tarjottu
44418.8.2019-Tarjottu
44519.8.2019-Tarjottu
44620.8.2019VapaaVapaa
44721.8.2019Uus tilaVapaa
44721.8.2019VapaaVapaa
44822.8.2019-Tarjottu
Perus_Pena
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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.

4348.8.2019Vapautumassa
4359.8.2019Vapautumassa
43610.8.2019Vapautumassa
43711.8.2019Vapautumassa
43812.8.2019Vapautumassa
43913.8.2019Vapaa
44014.8.2019Tarjottu
44115.8.2019Tarjottu
44216.8.2019Tarjottu
44317.8.2019Tarjottu
44418.8.2019Tarjottu
44519.8.2019Tarjottu
44620.8.2019Vapaa
44721.8.2019Vapaa
44822.8.2019Tarjottu
44923.8.2019Tarjottu
45024.8.2019Tarjottu
45125.8.2019Tarjottu
45226.8.2019Siirretään sopimukselle
45327.8.2019Sopimuksella

 

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
Specialist III
Specialist III

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