topic Re: combine 2 field in QlikView

combine 2 field

Anonymous — Mon, 12 Jan 2015 20:58:28 GMT

hi guys,

I have a file, where there is 2 field like city and country.

I'd like to calculate sore for country and cities in 1 table.

Score1	Score2	Score3	Score4
47	61	18	61
47	46	35	45
4	7	4	45
58	56	42	23
5	4	13	88
44	78	33	65
88	88	13	58
8	56	45	12
46	4	23	88
25	72	87	4
77	12	72	75
86	79	35	65
78	88	16	14
12	78	4	32
86	48	35	23
26	23	15	75
35	89	13	56
78	42	12	88

Please find my attached file.

Thanks,

Re: combine 2 field

jpapador — Mon, 12 Jan 2015 21:13:49 GMT

Can you be more specific in your attachment your inline table is one single table, so you can already calculate score in one table with both city and country if you add them both as dimensions.

Re: combine 2 field

reshmakala — Mon, 12 Jan 2015 21:14:15 GMT

Hi,

Modify your script as below:

Data:

LOAD* Inline [

name1,name2,Score1,Score2,Score3,Score4

aaaa,canada,4,7,4,45

sddssd,usa,58,56,42,23

sadfsdf,usa,5,4,13,88

cxgtry,canada,44,78,33,65

ty67,canada,88,88,13,58

ry67,usa,8,56,45,12

yrsafdg,usa,46,4,23,88

dgtru,usa,25,72,87,4

hgyui9,usa,77,12,72,75

gtrutyi,canada,86,79,35,65

htruityi,usa,78,88,16,14

gtruii,usa,12,78,4,32

wewerer,usa,86,48,35,23

dfghty,canada,26,23,15,75

thjuyi,canada,35,89,13,56

truy,usa,78,42,12,88];

LOAD name2 as name1

Resident Data;

Just add the last load statement for the fields that you wish to combine. The name1 now has both name2 and name1 values.

Re: combine 2 field

Anonymous — Mon, 12 Jan 2015 21:20:13 GMT

I combined 2 fields in 1, but there is no total count for countries

Re: combine 2 field

sunny_talwar — Mon, 12 Jan 2015 21:56:25 GMT

Is this what you are looking for? PFA

Best,

Re: combine 2 field

Anonymous — Mon, 12 Jan 2015 22:06:09 GMT

Looks like I wanted.

I hope it works for my purpose.

I need to only average, I need also %

Re: combine 2 field

sunny_talwar — Tue, 13 Jan 2015 02:18:52 GMT

What % are you looking for??

Best,

Re: combine 2 field

Anonymous — Tue, 13 Jan 2015 14:34:45 GMT

Also I have where there is tags Yes and No. I need also to show only Yes %.

Re: combine 2 field

sunny_talwar — Tue, 13 Jan 2015 14:40:02 GMT

It seems that you are talking about columns which you have not mentioned in your initial post. Would you be able to share a data source/qvw file which have all the columns you want to use in the thing you are trying to achieve.

Best,

Re: combine 2 field

reshmakala — Tue, 13 Jan 2015 14:43:52 GMT

What is the condition for the tags Yes and No?

Re: combine 2 field

Anonymous — Tue, 13 Jan 2015 14:47:00 GMT

Sorry about it.

I added Score5 (Yes, No and missing value)

Thank you,

Re: combine 2 field

Anonymous — Tue, 13 Jan 2015 14:57:15 GMT

I just modified the test file.

I want to see % of Yes for each tag and also average of % for countries.

Re: combine 2 field

sunny_talwar — Tue, 13 Jan 2015 14:59:07 GMT

And how is percentage calculated? Can you give an example from your database?

Best,

Re: combine 2 field

Anonymous — Tue, 13 Jan 2015 15:04:19 GMT

I usually calculate"

dimension -Name1

Count({<Score5={'Yes'}>} Name1)/ Count (Total Name1)

and the same for Name2

But I don't know how to combine 2 fields in 1

Re: combine 2 field

sunny_talwar — Tue, 13 Jan 2015 18:27:04 GMT

This may not be the best way to do this, but nonetheless PFA

Best,