topic Re: attribute comparisons in QlikView

attribute comparisons

Wed, 16 Sep 2015 21:58:38 GMT

Hello Gurus,

I have an interesting problem if someone can help me to find a solution. I have columns A, B and C which have attributes comparisons such as

user A B C

1 attr1-attr2 attr1-attr3 attr2-attr3

2 attr3-attr2 attr1-attr2 attr3-attr1

so in the first record, user chose attribute 1 over attribute 2, attribute 1 over attribute 3 and attribute 2 over attribute 3. I would like to have a table for each user such as

user attr1 attr2 attr3

1 1+1 -1+1 -1-1

2 1-1 -1-1 +1+1

of course my calculations are more complicated than this but this explains the problem easily. Is there a way to do this?

thanks,

Manoj

Re: attribute comparisons

Wed, 16 Sep 2015 22:49:45 GMT

Just to clarify, the correct display would be

user attr1 attr2 attr3

1 2 0 -2

2 0 -2 +2

Re: attribute comparisons

Digvijay_Singh — Thu, 17 Sep 2015 06:00:35 GMT

See attached -

Input:

Load * inline [

user,A,B,C

1,attr1-attr2, attr1-attr3, attr2-attr3

2,attr3-attr2, attr1-attr2, attr3-attr1

];

Output:

Load user,

SubStringCount(FullString,'+attr1')-SubStringCount(FullString,'-attr1') as attr1,

SubStringCount(FullString,'+attr2')-SubStringCount(FullString,'-attr2') as attr2,

SubStringCount(FullString,'+attr3')-SubStringCount(FullString,'-attr3') as attr3;

Load user,

' ' as attr1,

' ' as attr2,

' ' as attr3,

'+' & A & '+' & B & '+' & C as FullString

Resident Input;

Re: attribute comparisons

Thu, 17 Sep 2015 16:38:59 GMT

Hi Digvijay,

Thanks for your help but looks like I stated my problem a bit too simplistically. Actual data looks something like this (this is only part of the data as there are around 2000 records currently and growing)

record_id	qa11	qa12	qa13	qa14	qa15	qa16	qa17	qa18
23445049-8/19/2015-322-8/25/2015				attr2-attr8		attr1-attr5	attr2-attr4	attr1-attr7
23445147-8/22/2015-322-8/25/2015			attr6-attr4	attr6-attr5	attr8-attr4	attr5-attr1	attr6-attr7	attr2-attr1
23445547-9/4/2015-322-9/9/2015	attr1-attr5	attr8-attr7	attr3-attr5	attr6-attr4	attr8-attr1	attr2-attr6	attr1-attr4	attr2-attr4
24987848-8/29/2015-542-9/1/2015	attr5-attr7	attr3-attr4	attr1-attr2	attr3-attr1	attr6-attr2	attr8-attr3	attr6-attr5	attr1-attr4
484255-8/24/2015-617-8/27/2015			attr8-attr1		attr2-attr8		attr2-attr1
484264-8/24/2015-617-8/26/2015	attr2-attr3	attr2-attr4	attr6-attr2	attr3-attr5	attr7-attr8	attr6-attr8	attr1-attr4	attr6-attr1
484556-8/29/2015-617-9/2/2015	attr6-attr5	attr3-attr1	attr2-attr4	attr8-attr1	attr2-attr4	attr3-attr6	attr3-attr8	attr7-attr5
484636-8/30/2015-617-9/5/2015	attr5-attr4	attr6-attr7	attr8-attr4	attr1-attr7	attr3-attr1	attr8-attr2	attr2-attr3	attr6-attr4
484703-8/31/2015-617-9/3/2015	attr5-attr8	attr7-attr8	attr8-attr3	attr6-attr4	attr3-attr2	attr1-attr4	attr6-attr5	attr3-attr2
485075-9/5/2015-617-9/8/2015	attr1-attr7	attr5-attr6	attr3-attr7	attr3-attr4	attr2-attr5	attr2-attr6	attr8-attr5	attr3-attr4
485082-9/5/2015-617-9/9/2015	attr1-attr2	attr3-attr8	attr2-attr7	attr5-attr1	attr8-attr4	attr4-attr1	attr7-attr5	attr6-attr3
24948789-8/9/2015-617-8/12/2015	attr1-attr3	attr7-attr4	attr2-attr4	attr3-attr4	attr6-attr5	attr8-attr1	attr5-attr7	attr2-attr8
24948934-8/11/2015-617-8/14/2015		attr3-attr6	attr5-attr8	attr6-attr1		attr2-attr6
24949264-8/16/2015-617-8/19/2015	attr2-attr5	attr8-attr1	attr4-attr3	attr3-attr5	attr6-attr3	attr6-attr7	attr8-attr4	attr6-attr2
24949335-8/17/2015-617-8/19/2015	attr5-attr7	attr7-attr4	attr8-attr4	attr1-attr5	attr6-attr4	attr2-attr5	attr8-attr7	attr3-attr2
24949361-8/17/2015-617-8/19/2015	attr8-attr3	attr1-attr3	attr2-attr4	attr2-attr3	attr8-attr5	attr7-attr5	attr7-attr6	attr6-attr4

so, you see, neither rows number nor column contents are fixed so i cannot use load inline. Only the attribute numbers (8) are fixed. I am looking for a way where I can read each row and perform the calculations as shown in my first post.

regards,

Manoj

Re: attribute comparisons

Digvijay_Singh — Fri, 18 Sep 2015 02:20:24 GMT

Hi,

As long as your attributes and no of columns are fixed, this solution can be tweaked. Reading from excel or csv is not a problem, it can handle dynamic no of rows.

Please see attached the updated one based on new data.

Re: attribute comparisons

Fri, 18 Sep 2015 03:06:17 GMT

Thanks again Digvijay but I am using personal edition of QlikView so cannot open the file. Can you attach the script as text?

regards,

Manoj

Re: attribute comparisons

Digvijay_Singh — Fri, 18 Sep 2015 03:12:49 GMT

Here it is -

Input:

LOAD record_id,

qa11,

qa12,

qa13,

qa14,

qa15,

qa16,

qa17,

qa18

FROM

[https://community.qlik.com/message/868783]

(html, codepage is 1252, embedded labels, table is @1);

Output:

Load record_id,

SubStringCount(FullString,'+attr1')-SubStringCount(FullString,'-attr1') as attr1,

SubStringCount(FullString,'+attr2')-SubStringCount(FullString,'-attr2') as attr2,

SubStringCount(FullString,'+attr3')-SubStringCount(FullString,'-attr3') as attr3,

SubStringCount(FullString,'+attr4')-SubStringCount(FullString,'-attr4') as attr4,

SubStringCount(FullString,'+attr5')-SubStringCount(FullString,'-attr5') as attr5,

SubStringCount(FullString,'+attr6')-SubStringCount(FullString,'-attr6') as attr6,

SubStringCount(FullString,'+attr7')-SubStringCount(FullString,'-attr7') as attr7,

SubStringCount(FullString,'+attr8')-SubStringCount(FullString,'-attr8') as attr8;

Load record_id,

'+' & qa11 & '+' & qa12 & '+' & qa13 & '+' & qa14 & '+' & qa15 & '+' & qa16 & '+' & qa17 & '+' & qa18 as FullString

Resident Input;

Re: attribute comparisons

Fri, 18 Sep 2015 23:09:37 GMT

Excellent solution Digvijay! Thanks!

Let me throw another curve ball at you, is there a way to normalize the results for each row using the scale of 0 to 10? So if values for one of the responses are

1., 0.75, 1, 1.5, 0, 0.5, 1.25, -0.5

using the normalization formula, the first value would be

1-(-0.5) / 1.5-(-0.5) * 10 = 7.5

second, 0.75-(-0.5) / 1.5-(-0.5) * 10 = 6.25 and so on.

thanks,

Manoj

Re: attribute comparisons

Digvijay_Singh — Sat, 19 Sep 2015 12:17:00 GMT

Hi,

Can you define the formula further?, I need to know if its taking min and

max value of a row in calculation. If formula is same across, I think it

can be done.. Pl share formula explaining the meaning of each attribute.

Re: attribute comparisons

Digvijay_Singh — Sat, 19 Sep 2015 14:42:10 GMT

Pl chk if you can achieve the requirement by making changes in below base script, I am not sure if I understood the formula properly. Just pasting the output table script which I changed as per new requirement.

Output:

Load

(attr1-MinValue)/(MaxValue-MinValue)*10 as attr1,

(attr2-MinValue)/(MaxValue-MinValue)*10 as attr2,

(attr3-MinValue)/(MaxValue-MinValue)*10 as attr3,

(attr4-MinValue)/(MaxValue-MinValue)*10 as attr4,

(attr5-MinValue)/(MaxValue-MinValue)*10 as attr5,

(attr6-MinValue)/(MaxValue-MinValue)*10 as attr6,

(attr7-MinValue)/(MaxValue-MinValue)*10 as attr7,

(attr8-MinValue)/(MaxValue-MinValue)*10 as attr8;

Load *, rangemin(attr1,attr2,attr3,attr4,attr5,attr6,attr7,attr8) as MinValue,

rangemax(attr1,attr2,attr3,attr4,attr5,attr6,attr7,attr8) as MaxValue;