Create a flag for duplicate values

[analovera]

Hello,

I am looking for a way to identify duplicate values in Qliksense.

In the app I am building I have:

• Office
• Hotel
• Timestamp
• Number of Bookings

If an Office has 2 bookings for the same Hotel and with the same Timestamp, then it means that the Office booked 2 rooms in that Hotel simultaneously.

I have made a concatenation of "Offices"&"Hotel"&"Timestamp" and I would like to create a flag called "Multiroom".

• If "Offices"&"Hotel"&"Timestamp" is unique, then "Multiroom" is "No"
• If "Offices"&"Hotel"&"Timestamp" is repeated, then "Multiroom" is "Yes"

With this, I would create a table with Multiroom Yes/No and the Sum(Number of Bookings) in each category.

The are infinite combinations of "Offices"&"Hotel"&"Timestamp", so I can't use the INLINE formula in the script as mentioned in this thread: https://community.qlik.com/t5/New-to-QlikView/Identifying-duplicate-records-using-the-script/td-p/60...

Could anyone help?

Thank you

[dazerano]

In this scenario I would Create an aggregated mapping table first to count the rows and then use conditional logic when loading in the data to determine the multi-room status.

Example:

Map_Count_Unique_Rows:
Mapping
Load
Offices & '-' & Hotel & '-' & Timestamp As Lookup_Booking,
COUNT(Offices & '-' & Hotel & '-' & Timestamp) As No_of_Bookings
FROM [lib://.......qvd]
(qvd)
GROUP BY
Offices ,
Hotel ,
Timestamp;

LOAD
IF( APPLYMAP('Map_Count_Unique_Rows',Offices & '-' & Hotel & '-' & Timestamp,0)>1 ,'Yes','No') as Multiroom 

                               

FROM [lib://.......qvd]

[analovera · Accepted solution]

Thank you Dazerano, finally I managed to do it in a different way. This is the script I used:

 Table1

LOAD
OFFICES,
HOTEL,
TIMESTAMP,
"Number of Booking",
"OFFICES"&"HOTEL&"TIMESTAMP " as "CONCATENATE"

from....QVD

//CREATE AN INTERMEDIATE TABLE TO GROUP BY CONCATENATE
Qualify *;
MULTI_:
LOAD
CONCATENATE,
Sum("Number of Booking") as "Booking"
Resident Table1
Group by [CONCATENATE];
Unqualify *;

//SELECT ONLY THOSE RECORDS WITH MORE THAN ONE BOOKING
MULTI:
LOAD
MULTI_.CONCATENATE AS "CONCATENATE",
MULTI_.Booking AS "MultiroomBookings"
Resident MULTI_
Where MULTI_.Booking <> '1';

//DELETE INTERMEDIATE TABLE
DROP TABLE MULTI_;