Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Somewhat new to Qlikview and I've run into a question which I'm sure has a simple answer, but I can't quite figure it out. In the attached spreadsheet (my raw data) I have three tabs:
ORIGINAL DATA
ORGANIZATION AFTER CROSSTABLE (rows E-I transposed into "measurement" and "value" columns):
Next step/question: which functions can I use to 1) create a column for the color, 2) pull the color from column F, and 3) clean-up column F to show only the measurement type?
This newbie greatly appreciates your feedback.
Try this:
Transportation:
CrossTable(MEASUREMENT, VALUE, 4)
LOAD COUNTRY,
TRANSPORTATION,
UNIQUE_USERS,
DATA_MONTH,
BLUE_SAMPLESIZE,
BLUE_AVG,
RED_SAMPLESIZE,
RED_AVG
FROM
[SampleFile-kimrca.xlsx]
(ooxml, embedded labels, table is [ORIGINAL DATA]);
FinalTable:
LOAD COUNTRY,
TRANSPORTATION,
UNIQUE_USERS,
DATA_MONTH,
SubField(MEASUREMENT, '_', 1) as COLOR,
SubField(MEASUREMENT, '_', 2) as MEASUREMENT,
VALUE
Resident Transportation;
DROP Table Transportation;
Try this:
Transportation:
CrossTable(MEASUREMENT, VALUE, 4)
LOAD COUNTRY,
TRANSPORTATION,
UNIQUE_USERS,
DATA_MONTH,
BLUE_SAMPLESIZE,
BLUE_AVG,
RED_SAMPLESIZE,
RED_AVG
FROM
[SampleFile-kimrca.xlsx]
(ooxml, embedded labels, table is [ORIGINAL DATA]);
FinalTable:
LOAD COUNTRY,
TRANSPORTATION,
UNIQUE_USERS,
DATA_MONTH,
SubField(MEASUREMENT, '_', 1) as COLOR,
SubField(MEASUREMENT, '_', 2) as MEASUREMENT,
VALUE
Resident Transportation;
DROP Table Transportation;