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]

Hi Digvijay,

Thanks for all the help.

Yes, the formula should consider min and max for each row.

Here is the min-max normalization formula.

Normalized value of V = (V - min value of row)/(max value of row - min value of row) * (new max value of row - new min value of row) + (new min value of row).

In my example above, -0.5 is the minimum value of row, 1.5 is the max value of row, 0 is new min value and 10 is the new max value so the normalized value of 1 st value 1 will be

V = ( 1 -  (-0.5))/(1.5 - (-0.5)) * (10 - 0) + 0

V = 7.5

Please let me know if something is not clear.

regards,

Manoj

[Digvijay_Singh]

Hi,

Now Formula is clear, script is changed accordingly but your recent test data row won't help to verify as we are reading data in the form of 'attr1-attr2'. See if you can test it and verify.

Let vNewMin=0;

Let vNewMax=10;

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,

  (attr1-MinValue)/(MaxValue-MinValue)*($(vNewMax)-$(vNewMin))+$(vNewMin) as attr1,

  (attr2-MinValue)/(MaxValue-MinValue)*($(vNewMax)-$(vNewMin))+$(vNewMin) as attr2,

  (attr3-MinValue)/(MaxValue-MinValue)*($(vNewMax)-$(vNewMin))+$(vNewMin) as attr3,

  (attr4-MinValue)/(MaxValue-MinValue)*($(vNewMax)-$(vNewMin))+$(vNewMin) as attr4,

  (attr5-MinValue)/(MaxValue-MinValue)*($(vNewMax)-$(vNewMin))+$(vNewMin) as attr5,

  (attr6-MinValue)/(MaxValue-MinValue)*($(vNewMax)-$(vNewMin))+$(vNewMin) as attr6,

  (attr7-MinValue)/(MaxValue-MinValue)*($(vNewMax)-$(vNewMin))+$(vNewMin) as attr7,

  (attr8-MinValue)/(MaxValue-MinValue)*($(vNewMax)-$(vNewMin))+$(vNewMin) 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;

[#4]

Thanks for all the help Digvijay! I am busy with a release this week so will get back to this only next week. Will let you know how it goes. thanks again!

[Digvijay_Singh]

No probs. Curved Ball is in your court now.

[#6]

Works perfect Digvijay! You are quite good at this. Where do you work?