topic Re: Data Manipulation (Switch Measure for Dimension) in QlikView

Data Manipulation (Switch Measure for Dimension)

akpofureenughwu — Tue, 16 May 2017 14:31:16 GMT

Hello everyone.. I have a set of data in this format...

Location	Year	Quarter	UA	UC	RTA
Area 1	2015	Q1	12	1	2
Area 2	2015	Q1	13	1	0
Area 1	2015	Q2	34	2	1
Area 2	2015	Q2	13	2	1
Area 1	2015	Q3	13	1	0
Area 2	2015	Q3	1	2	0

The above table is the Health ,safety and Environment stats for a store with two branches....

I need to represent the data in a pyramid form and a bar chart form.. see attached for clarity..

I don't know how to manipulate the data (using UA, UC as dimension and Quarter as Measures) see picture

thanks

Re: Data Manipulation (Switch Measure for Dimension)

Anil_Babu_Samineni — Tue, 16 May 2017 15:53:15 GMT

I assume, Funnel chart don't capture the Text on Metric.

Re: Data Manipulation (Switch Measure for Dimension)

akpofureenughwu — Wed, 17 May 2017 08:26:45 GMT

roharoha‌ stalwar1‌jimhalpert‌

With the above table, How can I build a bar chart that has UA, UC as dimensions and Quarter as measure...

I used dimension as UA and count(quarter) but it doesn't the right result that matchs the visual in the attached file...

The desired visual

Use UA as dimension

Use Quarter as measures

To count the occurence of UA in each quarter...

Regards

Re: Data Manipulation (Switch Measure for Dimension)

Anonymous — Wed, 17 May 2017 19:18:53 GMT

I think you'll have to transform your data (Quarter as measure makes really no sense )

TEMP:
LOAD * INLINE [
Location, Year, Quarter, UA, UC, RTA
Area 1, 2015, Q1, 12, 1, 2
Area 2, 2015, Q1, 13, 1, 0
Area 1, 2015, Q2, 34, 2, 1
Area 2, 2015, Q2, 13, 2, 1
Area 1, 2015, Q3, 13, 1, 0
Area 2, 2015, Q3, 1, 2, 0
];
//Transform your data
FINAL:
LOAD Location, Year, Quarter, UA as Value, 'UA' as Type Resident TEMP;
LOAD Location, Year, Quarter, UC as Value, 'UC' as Type Resident TEMP;
LOAD Location, Year, Quarter, RTA as Value, 'RTA' as Type Resident TEMP;
DROP Table TEMP;

Re: Data Manipulation (Switch Measure for Dimension)

akpofureenughwu — Wed, 17 May 2017 19:46:50 GMT

This is my desired result but the data transformation is quite cumbersome..... I have about 385 rows, 11 columns of data to import to qlikview... Is there any way I can transform the data?

roharoha‌ Thank you sir for your guide...

Regards

Re: Data Manipulation (Switch Measure for Dimension)

Anonymous — Wed, 17 May 2017 19:55:36 GMT

You have to do it the same way as in my example...

You need 11 lines like that:

LOAD Location, Year, Quarter, MEASURE1 as Value, 'MEASURE1' as Type Resident TEMP;

concatenate

LOAD Location, Year, Quarter, MEASURE2 as Value, 'MEASURE1' as Type Resident TEMP;

,,,

concatenate

LOAD Location, Year, Quarter, MEASURE11 as Value, 'MEASURE11' as Type Resident TEMP;

... I can't help you any further without sample data.

Re: Data Manipulation (Switch Measure for Dimension)

akpofureenughwu — Wed, 17 May 2017 20:04:53 GMT

roharoha‌

I can do what you did in the above example... but my data source is SQL...

With your approach, I have to manually transform the data...

Is it possible to transform this data from an external source like sql or even an excel file?

Re: Data Manipulation (Switch Measure for Dimension)

Anonymous — Wed, 17 May 2017 20:13:17 GMT

You can transform SQL Tables the same way as Excel Files or Inline Tables...

I can only give you an idea how to solve it...

Re: Data Manipulation (Switch Measure for Dimension)

Digvijay_Singh — Wed, 17 May 2017 20:15:20 GMT

Not sure but did you try transforming through cross table?

Re: Data Manipulation (Switch Measure for Dimension)

romansavchuk — Wed, 17 May 2017 20:18:21 GMT

You can use crosstable in following way:

TEMP:

CrossTable(Type, Value, 3)

LOAD * INLINE [

Location, Year, Quarter, UA, UC, RTA

Area 1, 2015, Q1, 12, 1, 2

Area 2, 2015, Q1, 13, 1, 0

Area 1, 2015, Q2, 34, 2, 1

Area 2, 2015, Q2, 13, 2, 1

Area 1, 2015, Q3, 13, 1, 0

Area 2, 2015, Q3, 1, 2, 0

];

Re: Data Manipulation (Switch Measure for Dimension)

Digvijay_Singh — Wed, 17 May 2017 20:23:21 GMT

Check this may be -

Re: Data Manipulation (Switch Measure for Dimension)

Anonymous — Wed, 17 May 2017 20:24:45 GMT

yes off course, crosstable load is also an option... but the table must be in a specific format as we know

TEMP:
LOAD * INLINE [
Location, Year, Quarter, UA, UC, RTA
Area 1, 2015, Q1, 12, 1, 2
Area 2, 2015, Q1, 13, 1, 0
Area 1, 2015, Q2, 34, 2, 1
Area 2, 2015, Q2, 13, 2, 1
Area 1, 2015, Q3, 13, 1, 0
Area 2, 2015, Q3, 1, 2, 0
];

FINAL:
CrossTable(Type, Value, 3)
LOAD Location,
Quarter,
Year,
RTA,
UA,
UC
Resident TEMP;

DROP Table TEMP;

Re: Data Manipulation (Switch Measure for Dimension)

Digvijay_Singh — Wed, 17 May 2017 20:30:19 GMT

Yup, I agree.

Re: Data Manipulation (Switch Measure for Dimension)

akpofureenughwu — Thu, 18 May 2017 09:37:42 GMT

This is great with the use of crosstable I have managed to transfrom 4,000 lines of data ( automatically too).. but I need to sort my data from (highest to lowest).. used expression.....

Pick(Match(ValueList('UA','UC','RTA'),'UA','UC','RTA'),Sum({<Key={'UA'}>}Value),Sum({<Key={'UC'}>}Value),Sum({<Key={'RTA'}>}Value))

but there was not change?

Thanks