attribute comparisons

[#1]

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

[#2]

Just to clarify, the correct display would be

user      attr1          attr2          attr3

1          2                 0               -2

2          0                -2              +2

[Digvijay_Singh]

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;

[#4]

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

[Digvijay_Singh]

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.

[#6]

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

[Digvijay_Singh · Accepted solution]

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;

[#8]

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

[Digvijay_Singh]

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.

[Digvijay_Singh]

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;

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;