2 Replies Latest reply: Feb 10, 2017 12:24 PM by Hans Will RSS

    Combining Crosstab data into single field

    Hans Will

        New to the forum & relatively new to Qlik...please offer guidance if I'm outside the process...thank you.

         

      QUALITY_LABELING_ERRORQUALITY_SHIPMENT_ERRORQUALITY_PACKAGING_ERROR
      010
      001
      001
      010
      010
      001
      100

       

       

      I would like to consolidate the data above into a single column:

      Where cell content >0 then the field = the column header.

       

      Here's the desired output using the data above:

       

      Header 1ERROR TYPE
      Record1QUALITY_SHIPMENT_ERROR

      Record2

      QUALITY_PACKAGING_ERROR
      Record3QUALITY_PACKAGING_ERROR
      Record4QUALITY_SHIPMENT_ERROR
      Record5QUALITY_SHIPMENT_ERROR
      Record6QUALITY_PACKAGING_ERROR
      Record7QUALITY_LABELING_ERROR
        • Re: Combining Crosstab data into single field
          John Sakalis

          Try this:

           

          Data:

          LOAD * INLINE [

          QUALITY_LABELING_ERROR, QUALITY_SHIPMENT_ERROR, QUALITY_PACKAGING_ERROR,

          0, 1, 0,

          0, 0, 1,

          0, 0, 1,

          0, 1, 0,

          0, 1, 0,

          0, 0, 1,

          1, 0, 0];

           

           

          Data2:

          CROSSTABLE ([ERROR TYPE],Value) LOAD

          'Record' & Num(RowNo(),'00') as Record,

          *

          RESIDENT Data;

           

           

          Data3:

          NOCONCATENATE LOAD

            *

          RESIDENT Data2

          WHERE Value=1;

           

           

          DROP TABLE Data;

          DROP TABLE Data2;

            • Re: Combining Crosstab data into single field
              Hans Will

              John,

               

              Thanks for the reply...I need to study it more. As you can see below, I've concatenated them in the load script...then I will label the concatenated result in the visualizations. I'm sure there's a cleaner way to do this, but this appears to be working.

               

              LOAD

                  CREATIONTS,

                  ENT,

                  GRP,

                  COMP,

                  ARTICLE,

                  LOCATION,

                  SAVEDBY,

                  DEPARTMENT,

                  MITARBEITER,

                  ROOKIE AS ISROOKIE,

                  DELIVERYNOTE,

                  DELIVERYTYPE,

                  CUSTOMERNO,

                  CUSTOMERNAME,

                  PREMIUMFLAG,

                  SUPPLIERNAME1,

                  SUPPLIERNAME2,

                  DRYPACK AS ISDRYPACK,

                  ARTICLEDESCRIPTION,

                  WEIGHTART AS ISWEIGHTART,

                  PICKEDQTY,

                  PICKQTY,

                  ERRORDATE,

                  YearName("ERRORDATE") as Year,

                  MonthName("ERRORDATE")as Month,

                DayName("ERRORDATE")as Day,

                  WeekName("ERRORDATE") as Week,

                  QuarterName("ERRORDATE")as Quarter,

                  Z AS COUNT_ERROR,

                  Q AS QUALITY_ERROR,

                  "T" AS TYPE_ERROR,

                  "ok or not ok" AS UNABLE_TO_DETERMINE_ERROR,

                  SHIPMENT_E,

                 Q_LABELING_E &

                  Q_SPECIALHANDLING_E &

                  Q_SHIPMENT_E &

                  Q_PACKAGING_E &

                  Q_OTHERS_E as ERROR_TYPE,

                  INEXTERN,

                  "WH #" AS WHNUMBER,

                  "PICKERPACKER E" AS PICKERPACKERERROR,

                  EZ AS EINZEILER,

                  MZ AS MEHRZEILER,

                  HRL1 AS HRL1PICK,

                  HRL2 AS  HRL2PICK,

                  LAGERORT AS QUELL_LAGERORT,

                  "ID OPERATOR" AS IDOPERATOR

              FROM [lib://xxx/YYY*.xlsx]

              (ooxml, embedded labels, table is Result);