topic Re: qlik issue in QlikView

qlik issue

Anonymous — Thu, 17 Aug 2017 06:02:38 GMT

i have a field x and y

y is a flag with values n and y

but for few of the values in x field am getting both n and y values because of this there is an isue wwith the expressions total values

so i want to rectify this y field ,

x y

23 n

23 y

like this

it should ideally be havinf only one value assigned

Re: qlik issue

tresB — Thu, 17 Aug 2017 06:07:07 GMT

And out of n and y, which one would you like to keep?

Re: qlik issue

Anonymous — Thu, 17 Aug 2017 06:11:11 GMT

actually what i want for any of the values in x the y shud either be y or n

this is the issue with the data ,

so may be if we can assign some integer values or so ,?

Re: qlik issue

tresB — Thu, 17 Aug 2017 06:23:26 GMT

I guess, if you have a look into the data set in a broader view, you would probably be able to find a logic to decide upon - 'y' or 'n' - which to keep or even if they are at all irrelevant or not. If not, and you just can remove any of them, you could probably first identify the duplicates and remove them using exists() or distinct load or even hard code loading for the y field. Try to provide a sample data set (with similar to real data) to get more specific help on how to remove duplicates.

Re: qlik issue

kenphamvn — Thu, 17 Aug 2017 06:31:10 GMT

Try this

TestTable:
LOAD * INLINE [
    Col_X, Col_Y
    A, y,
    B, ny
    C, yn
    D, n
];


NoConcatenate
Table:
load Col_X,Col_Y
Resident TestTable
where Len(Col_Y)=1;


Concatenate
load Col_X, SubField(Left(Col_Y,1)&'/'&Right(Col_Y,1),'/') as Col_Y
Resident TestTable
where Len(Col_Y)=2;


Drop Table TestTable

Re: qlik issue

Anonymous — Thu, 17 Aug 2017 06:35:25 GMT

do u mean to say :

x y

23 n

23 y

i shud hardcode for all these x values as ny or yn?

Re: qlik issue

kenphamvn — Thu, 17 Aug 2017 06:52:41 GMT

Sorry i miss understood.

Please try again

TestTable:
LOAD * INLINE [
    Col_X, Col_Y
    A, y,
    B, n
    A, y
    D, n
    B, y
];


NoConcatenate
Table:
load Col_X,if (len( Concat(Col_Y) ) >1,'X',Concat(Col_Y)) as Col_Y
Resident TestTable
group by Col_X
;
Drop Table TestTable

Re: qlik issue

Anonymous — Thu, 17 Aug 2017 07:24:12 GMT

thanks so much for your response and help,

but need to understand what do you mean by 'X' over here?

or if u can share sample what will the output data for this?

please

Re: qlik issue

kenphamvn — Thu, 17 Aug 2017 07:29:24 GMT

'X' is default values when Col_X has both n and y values

Output here

Re: qlik issue

Anonymous — Thu, 17 Aug 2017 07:49:07 GMT

ok,thanks so much for the help,

actually am using this coly values in my expressions as:count({$<Col_Y={'N'}>}Col_X)

count({$<Col_Y={'Y'}>}Col_X)

count(distinct Col_X), the total value here shud be sum of two abve ,

col_x has integer values