sub sql

[#1]

Hi,

Exists this the same SQL command  or similar in qlikview, in load  or in set analysis?

SELECT TEXT,

                (SELECT SUM(TOTAL) AS TOTAL FROM X AS Y WHERE Y.TEXT LIKE X.TEXT + '%' ) AS TOTAL

FROM X

[nilesh_gangurde]

This you can achiev in the set analysis.

-Nilesh

[#3]

Can you show an example?

[nilesh_gangurde]

Please provide sample data.

-Nilesh

[#5]

X:

LOAD TEXT, TOTAL INLINE

[TEXT, TOTAL

"12250/20207", 0

"12250/20207/5754", 9.63

"12250/20207/12267", 3.42

"12250/20207/35426", 0

"12250/20207/35426/35734", 0

"12250/20207/35426/35734/1320", 0.19

"12250/20207/35426/35734/5005", 0.21

"12250/20207/35426/35734/5014", 5.4

"12250/20207/35426/35734/5079", 0.04

"12250/20207/35426/35734/6717", 0.7

"12250/20207/35426/35734/7708", 10.27

"12250/20207/35426/35734/13706", 0.17

"12250/20207/36139", 0]

[#6 · Accepted solution]

I have created an sample qvw.

I have used two methods. 

First, I have separated values and gave a name in each value using subfield() function. 

Second, I have used wildmatch() function to pick each TEXT and its Total.

Hopefully, it is what you are trying to achieve.

Sean

[#7]

Thanks Sean!

But, do you know a way to do this to off load, for example with set analysis?

[#8]

I was trying to add Set Analysis one in the example, but I was not able to come up with a good solution.

[#9]

yes, is a very difficult this solution.

Thanks your help!

[#10]

Hi Mr Sean,

After many hours and your tips, i found a solution.

See attached. What did you think for my solution?