topic Re: Combining Crosstab data into single field in App Development

Combining Crosstab data into single field

Anonymous — Fri, 03 Feb 2017 20:00:41 GMT

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

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

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 1	ERROR TYPE
Record1	QUALITY_SHIPMENT_ERROR
Record2	QUALITY_PACKAGING_ERROR
Record3	QUALITY_PACKAGING_ERROR
Record4	QUALITY_SHIPMENT_ERROR
Record5	QUALITY_SHIPMENT_ERROR
Record6	QUALITY_PACKAGING_ERROR
Record7	QUALITY_LABELING_ERROR

Re: Combining Crosstab data into single field

jsakalis — Mon, 06 Feb 2017 22:11:17 GMT

Try this:

Data:

LOAD * INLINE [

QUALITY_LABELING_ERROR, QUALITY_SHIPMENT_ERROR, QUALITY_PACKAGING_ERROR,

0, 1, 0,

0, 0, 1,

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

Anonymous — Fri, 10 Feb 2017 17:24:22 GMT

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