Create a single dimension from multiple fields

Question

So essentially I want to create a single dimension by using their respective column/filename, say a Listbox called Asset, to make a selection on this laptop, desktops, server, and tablet.

Many thanks.

enter image description here

Answer 1

What you'd want is the CrossTable prefix (see the Qlik Help here). It allows you to "pivot" a table so that 1 record with multiple columns becomes multiple records with just 2 columns (1 column for field name, 1 column for field value).

So given your table, which we'll call [Data]:

Code	# of laptop	# of desktop	# of servers	# of tablet
d1	0	1	0	1
a2	23	3	0	0
a3	12	5	0	0
f1	0	14	0	0
e3	0	12	0	0
z2	0	5	1	0

...you can use the following Qlik script in the Data Load Editor to get the desired output:

[Pivoted]:
CrossTable ([Device], [DeviceCount], 1) Load * Resident [Data];

Drop Table [Data];


[New Data]:
NoConcatenate Load
    [Code]
  , Capitalize(SubField([Device], ' ', -1)) as [Device]
  , [DeviceCount]
Resident [Pivoted];

Drop Table [Pivoted];

That should give you this result:

Code	Device	DeviceCount
a2	Desktop	3
a2	Laptop	23
a2	Servers	0
a2	Tablet	0
a3	Desktop	5
a3	Laptop	12
a3	Servers	0
a3	Tablet	0
d1	Desktop	1
d1	Laptop	0
d1	Servers	0
d1	Tablet	1
e3	Desktop	12
e3	Laptop	0
e3	Servers	0
e3	Tablet	0
f1	Desktop	14
f1	Laptop	0
f1	Servers	0
f1	Tablet	0
z2	Desktop	5
z2	Laptop	0
z2	Servers	1
z2	Tablet	0