peek and duplicated rows

[#1]

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

[trdandamudi]

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;

[sunny_talwar]

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?

[#4]

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;

[sunny_talwar]

See if this helps:

KundenTAB2:
NoConcatenate
LOAD DISTINCT

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

[MarcoWedel]

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

[#7]

Many Thanks for your Support!

[MarcoWedel]

you're welcome

please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco

[Clever_Anjos]

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

[#10]

because i create a new table.