Create a flag

[#1]

Hi,

I have tried in different ways but not with good result. Therefore I turn to the combined wisdom of community...

I have a table with visitors:

Visitors:
LOAD
            Left(Filename(), 3) as Omr,
(Filename()) as Reg,
            RecNo() as %Index,
            Patindex,
            Left(FileName(), 3) & '_' & Patindex as %IndexPnr,
            Left(FileName(), 3) & '_' & Patindex as %IndexPnrTest,
            VisitingDate,
            Left(FileName(), 3) & '_' & Patindex & '_' & VisitingDate as %IndexPnrBD,
            Year(MakeDate(Left(Besöksdatum, 4), Mid(VisitingDate, 5, 2), Mid(VisitingDate, 7, 2))) as År,
            Month(MakeDate(Left(Besöksdatum, 4), Mid(VisitingDate, 5, 2), Mid(VisitingDate, 7, 2))) as Månad,
            Day(MakeDate(Left(Besöksdatum, 4), Mid(VisitingDate, 5, 2), Mid(VisitingDate, 7, 2))) as Dag,
            WeekDay(MakeDate(Left(Besöksdatum, 4),          Mid(VisitingDate, 5, 2), Mid(VisitingDate, 7, 2))) as VeckoDag,
            If(Day(MakeDate(Left(Besöksdatum, 4), Mid(VisitingDate, 5, 2), Mid(VisitingDate, 7, 2))) = 25, 'Journal per 25:e') as Info,
     Signatur,
     Journal  as VG,  

%Index is unique for every visitor. But every visitor can appear multiple time in table. What's the best way to create a flag to be able to sum every unique visit with the same result as Count(DISTINCT %Index)?

Best regards

Torbjörn Ungvall (@Ungvall)

Senior Business Discovery Manager

Advectas AB

[#2]

Hi Torbjörn,

could you give an example: Input (maybe a bit simplified) and expected output?

Ciao

Klaus

[#3]

Thanks,

Uploaded a scrambled file. Sum(Flag) have to be same as Count(DISTINCT %Index)

Best regards

Torbjörn Ungvall (@Ungvall)

Senior Business Discovery Manager

Advectas AB

[frank_carlsson · Accepted solution]

Hi!

Is this what you're looking for?

[rbecher]

You could use exists() and dump the order by %Index..

- Ralf