topic Re: Data inconsistency in QlikView

Data inconsistency

nikhilgarg — Fri, 12 Jun 2015 06:08:00 GMT

Hey,

I have a qstn.

I have following data:

Col1 Col2 Col3 Col4 Col5 Total

212 ABC 0 12 0 12

212 ABC 0 11 0 11

212 ABC 0 0 9 9

212 ABC 64 0 0 64

I want all these multiple entries to come in a single line like:

Col1 Col2 Col3 Col4 Col5 Total

212 ABC 64 23 9 96

How can i obtain this??

thanks

Re: Data inconsistency

PrashantSangle — Fri, 12 Jun 2015 06:28:10 GMT

Hi,

What is your expression for Col3,Col4,Col5,Total???

Use Sum(Col3), Sum(Col4), Sum(Col5)

Regards

Re: Data inconsistency

robert_mika — Fri, 12 Jun 2015 06:31:41 GMT

t1:

load * Inline

[ Col1 ,Col2 , Col3, Col4 , Col5, Total

212 ,ABC , 0 , 12, 0, 12

212 ,ABC , 0 , 11 , 0 , 11

212 ,ABC , 0 , 0 , 9 , 9

212 ,ABC , 64, 0 , 0 , 64

]

;

t2:

LOAD

Col1 as col1,

Col2 as col2,

SUM(Col3) as col3,

Sum(Col4) as col4,

Sum(Col5) as col5,

Sum(Total) as total

Resident t1

Group by Col1,Col2;

drop Table t1;

Re: Data inconsistency

nikhilgarg — Fri, 12 Jun 2015 06:58:21 GMT

Hey ,

Thanks but col3, col4, col5 are my expressions and not dimensions so cannot

use it at script level.

Re: Data inconsistency

PrashantSangle — Fri, 12 Jun 2015 07:04:30 GMT

Hi,

What is the expression???

and do you have any extra dimension than only col1,col2

Regards

Re: Data inconsistency

robert_mika — Fri, 12 Jun 2015 07:05:02 GMT

SO as max suggested use your current columns with SUM()

Re: Data inconsistency

nikhilgarg — Fri, 12 Jun 2015 09:38:38 GMT

Hey Thanks but i want to show only State = 'Accepted'. How can i acheive this?

Re: Data inconsistency

robert_mika — Fri, 12 Jun 2015 09:43:53 GMT

Could post your app?

Re: Data inconsistency

nikhilgarg — Fri, 12 Jun 2015 09:49:01 GMT

PFA..

Re: Data inconsistency

PrashantSangle — Fri, 12 Jun 2015 10:01:16 GMT

What is your expected Output in provided example????

Regards

Re: Data inconsistency

nikhilgarg — Fri, 12 Jun 2015 10:05:11 GMT

Under State column i want only SPOT/FORWARD value

Re: Data inconsistency

robert_mika — Fri, 12 Jun 2015 10:08:22 GMT

Your data set does not contain those fields/values.

Re: Data inconsistency

nikhilgarg — Fri, 12 Jun 2015 12:49:44 GMT

Hey,

I have pivot table. In that i have an expressn(Col3). I want the data to be shown in pivot as per ascending value of Expressn colmn(col3) . How can i acheive this ?

Re: Data inconsistency

robert_mika — Fri, 12 Jun 2015 12:55:51 GMT

Look here:Sorting in pivot tables

Re: Data inconsistency

nikhilgarg — Sat, 13 Jun 2015 08:34:34 GMT

Hey,

Thanks but now it is showing rank for following values as:

%Accepted Rank

66.67% 1-3

50% 4

It sld be like :

Rank

how to acheive this?

Re: Data inconsistency

kaushiknsolanki — Sat, 13 Jun 2015 09:02:37 GMT

Try this.

=rank(total count({<Source = {'Accepted', 'ClientAccept', 'DealerAccept'}>}Source)/count({<Source = {'Accepted', 'ClientAccept', 'DealerAccept', 'Rejected', 'Timedout'}>}Source),0,1)

Regards,

Kaushik Solanki