Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Just to clarify, the correct display would be
user attr1 attr2 attr3
1 2 0 -2
2 0 -2 +2
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;
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
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.
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
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;
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
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.
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;