Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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
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
Hey Diego, Thanks for your help! I was able to break it down within a table.
Thank you!!!
Thanks Hector, I was able to follow Diego's way and break it down within a table.