Checking for Duplicates

[hammermill21]

Hello!

I am looking to count how many duplicate barcode number I have, this is my script:

[FireX Monthly Inspection]:

LEFT KEEP ([FireX Database])

LOAD ID AS [Firex Monthly Inspection Key],

// [PARENT_RECORD_ID] AS [PARENT_RECORD_ID],

// [PARENT_PAGE_ID] AS [PARENT_PAGE_ID],

// [PARENT_ELEMENT_ID] AS [PARENT_ELEMENT_ID],

  Date(Floor([CREATED_DATE], 1/(24 * 60)), 'YYYY-MM-DD hh:mm') AS [CREATED DATE],

    //Timestamp(Timestamp#([CREATED_DATE], 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm' ) AS [CREATED_DATE],

// [CREATED_BY] AS [CREATED_BY],

// [CREATED_LOCATION] AS [CREATED_LOCATION],

// [CREATED_DEVICE_ID] AS [CREATED_DEVICE_ID],

// [MODIFIED_DATE] AS [MODIFIED_DATE],

// [MODIFIED_BY] AS [MODIFIED_BY],

// [MODIFIED_LOCATION] AS [MODIFIED_LOCATION],

// [MODIFIED_DEVICE_ID] AS [MODIFIED_DEVICE_ID],

// [SERVER_MODIFIED_DATE] AS [SERVER_MODIFIED_DATE],

  Date(Floor(MonthEnd([CREATED_DATE])), 'MMM YYYY') AS [Inspection MMM YYYY],

    Num(Date(Floor(MonthEnd([CREATED_DATE])))) AS [Inspection Date Num],

  [equiploc] AS [equiploc],

  [inspweek] AS [inspweek],

  [inspmonth] AS [inspmonth],

  [inspyr] AS [inspyr],

  [tech] AS [tech],

  [pass] AS [pass],

  [reason] AS [reason],

  [pic] AS [pic],

  //[fixed] AS [fixed],

  [corrective_inspection] AS [corrective_inspection],

  [building] AS [building],

  [xtype] AS [xtype],

  [firex_size] AS [firex_size],

  [my_scan] AS [my_scan],

  [comments] AS [comments],

    Text([equip_id_text]) AS [Inspection Bar Code],

    Text([equip_id_text]) AS [Bar Code],

    Text([equip_id_text]) & '|' & Num(Date(Floor(MonthEnd([CREATED_DATE])))) AS [Inspection Key],

  [floor] AS [floor],

  [getdate] AS [getdate],

    Date(Date#([getdate], 'YYYY-MM-DD') ) AS [Get Date],   

  [enter_building] AS [enter_building],

  [room_number] AS [room_number],

  [enter_floor] AS [enter_floor],

  [is_there_a_barcode] AS [is_there_a_barcode],

  [__FK_record] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_record])

AND NOT IsNull([equip_id_text]);

//AND Exists([Bar Code],[equip_id_text]);

The bar code numbers are labeled under [my_scan] AS [my_scan].

Thanks!!!

[hector_munoz]

Hi Elisabeth,

If, after reloading the script (designer), you create a table with a dimension 'my_scan' and an expression like Count(my_scan) you will know how many times each my_scan value appears in the script table.

Regards,

H

[#3]

Hi Elizabeth

I think you can use something like this in your script:

[FireX Monthly Inspection]:

LEFT KEEP ([FireX Database])

LOAD ID AS [Firex Monthly Inspection Key],

.....

(all your script)

....

WHERE NOT IsNull([__FK_record])

AND NOT IsNull([equip_id_text]);

//AND Exists([Bar Code],[equip_id_text]);

//I will create a new table to count how many times a barcode appears

BCDuplicates:

Load

    my_scan,

    Count(my_scan) as BarCodeCount

Resident [FireX Monthly Inspection]

group by my_scan;

Then you can create a table (within Qlik Sense) with the columns my_scans and BarCodeCount and check values >= 2 in the BarCodeCount Column.

Hope this helps.

Regards

Diego

[hammermill21]

Hey Diego, Thanks for your help! I was able to break it down within a table.

Thank you!!!

[hammermill21]

Thanks Hector, I was able to follow Diego's way and break it down within a table.