topic Re: sub sql in QlikView

sub sql

Wed, 15 Jan 2014 13:14:13 GMT

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

Re: sub sql

nilesh_gangurde — Wed, 15 Jan 2014 13:16:08 GMT

This you can achiev in the set analysis.

-Nilesh

Re: sub sql

Wed, 15 Jan 2014 13:25:08 GMT

Can you show an example?

Re: sub sql

nilesh_gangurde — Wed, 15 Jan 2014 13:28:38 GMT

Please provide sample data.

-Nilesh

Re: sub sql

Wed, 15 Jan 2014 13:45:55 GMT

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]

Re: Re: sub sql

Wed, 15 Jan 2014 14:44:22 GMT

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

Re: Re: sub sql

Wed, 15 Jan 2014 16:36:23 GMT

Thanks Sean!

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

Re: Re: Re: sub sql

Wed, 15 Jan 2014 21:10:58 GMT

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

Re: Re: Re: sub sql

Thu, 16 Jan 2014 12:18:08 GMT

yes, is a very difficult this solution.

Thanks your help!

Re: Re: Re: Re: sub sql

Thu, 16 Jan 2014 17:03:09 GMT

Hi Mr Sean,

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

See attached. What did you think for my solution?

Re: sub sql

Thu, 16 Jan 2014 17:23:22 GMT

Very clever! I like your solution.