topic peek and duplicated rows in QlikView

peek and duplicated rows

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

i have the following Problem with the peek function.

My tbale is like:

KundenTAB2:

Name Kundennummer Rolle Status

Name1, 115702, Kontoinhaber, GELÖSCHT

Name2, 109981, Kontoinhaber, GELÖSCHT

Name3, 118297, Kontoinhaber,

than i execute the following code.

KundenTAB3:

NoConcatenate

LOAD Name,

Kundennummer,

Rolle,

Status,

if(previous(Name) = Name, 1,0) as %Flag

Resident KundenTAB2

;

and the reuslt is:

KundenTAB3

Name1, 115702, Kontoinhaber, GELÖSCHT 0

Name1, 115702, Kontoinhaber, GELÖSCHT 1

Name2, 109981, Kontoinhaber, GELÖSCHT 0

Name2, 109981, Kontoinhaber, GELÖSCHT 1

Name3, 118297, Kontoinhaber, 0

Name3, 118297, Kontoinhaber, 1

Why are the rows duplicated?

Thanks in advanced,

King regards,

Florian

Re: peek and duplicated rows

trdandamudi — Thu, 02 Feb 2017 16:10:05 GMT

Give a try on the below and you will get the correct results:

KundenTAB2:

Load * Inline [

Name, Kundennummer, Rolle, Status

Name1, 115702, Kontoinhaber, GELÖSCHT

Name2, 109981, Kontoinhaber, GELÖSCHT

Name3, 118297, Kontoinhaber

] ;

KundenTAB3:

NoConcatenate

LOAD Name,

Kundennummer,

Rolle,

Status,

if(previous(Name) = Name, 1,0) as %Flag

Resident KundenTAB2 ;

Drop Table KundenTAB2;

Re: peek and duplicated rows

sunny_talwar — Thu, 02 Feb 2017 16:13:53 GMT

I don't see this using this script:

KundenTAB2:

LOAD * Inline [

Name, Kundennummer, Rolle, Status

Name1, 115702, Kontoinhaber, GELÖSCHT

Name2, 109981, Kontoinhaber, GELÖSCHT

Name3, 118297, Kontoinhaber,

];

KundenTAB3:

NoConcatenate

LOAD Name,

Kundennummer,

Rolle,

Status,

if(previous(Name) = Name, 1,0) as %Flag

Resident KundenTAB2;

DROP Table KundenTAB2;

Do you may be have duplicate rows you don't know about?

Re: peek and duplicated rows

Thu, 02 Feb 2017 16:23:23 GMT

Thank you for the quick Response, but it do not work. Maybe it ist better to post the hole code:

KundenTAB:
LOAD upper(ADNAM1) as Name,
ADKDNR as Kundennummer,
ADSTAT
FROM
[..\0000_Resources\0100_QVD_Raw\K0ADR\K0ADR.ADRDAT.qvd]
(qvd)
;

left join
LOAD *
Resident Konto;

Drop Table Konto;

KundenTAB2:
NoConcatenate
LOAD Name,
Kundennummer,
Rolle,
if(ADSTAT ='Z','GELÖSCHT') as Status
Resident KundenTAB
order by Name, Kundennummer
;

drop Table KundenTAB;

KundenTAB3:
NoConcatenate
LOAD Name,
Kundennummer,
Rolle,
Status,
if(previous(Name) = Name, 1,0) as %Flag
Resident KundenTAB2
;

drop Table KundenTAB3;
exit Script;

Re: peek and duplicated rows

sunny_talwar — Thu, 02 Feb 2017 16:27:36 GMT

See if this helps:

KundenTAB2:
NoConcatenate
LOAD DISTINCT

Name,
Kundennummer,
Rolle,
if(ADSTAT ='Z','GELÖSCHT') as Status
Resident KundenTAB
order by Name, Kundennummer
;

Re: peek and duplicated rows

MarcoWedel — Fri, 03 Feb 2017 00:21:25 GMT

Hallo Florian,

like Sunny already noticed, there seem to be duplicate values in the KundenTAB2 already (that you might not see e.g. using a table box as this only shows distinct rows).

hope this helps

Gruß

Marco

Re: peek and duplicated rows

Fri, 03 Feb 2017 10:22:14 GMT

Many Thanks for your Support!

Re: peek and duplicated rows

MarcoWedel — Fri, 03 Feb 2017 23:39:32 GMT

you're welcome

please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco

Re: peek and duplicated rows

Clever_Anjos — Sat, 04 Feb 2017 00:14:59 GMT

Why do you create a table and drop it just afterwards?

KundenTAB3: #### creating
NoConcatenate
LOAD Name,
Kundennummer,
Rolle,
Status,
if(previous(Name) = Name, 1,0) as %Flag
Resident KundenTAB2
;
drop Table KundenTAB3; ### Dropping, guessing you myistyped KundenTAB2

Re: peek and duplicated rows

Fri, 10 Feb 2017 11:15:28 GMT

because i create a new table.