4 Replies Latest reply: Mar 7, 2017 10:51 AM by Elizabeth Viso RSS

    Checking for Duplicates

    Elizabeth Viso

      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!!!

        • Re: Checking for Duplicates
          Muñoz Héctor

          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

          • Re: Checking for Duplicates
            Diego Vega

            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