Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
hanswill
New Contributor

Combining Crosstab data into single field

  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
2 Replies
jsakalis
New Contributor III

Re: Combining Crosstab data into single field

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;

hanswill
New Contributor

Re: Combining Crosstab data into single field

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);

Community Browser