qlik issue

[Anonymous]

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

[tresesco]

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

[Anonymous]

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 ,?

[tresesco]

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.  

[kenphamvn]

Hi

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

[Anonymous]

do u mean to say :

x      y

23    n

23    y

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

[kenphamvn]

Hi

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

[Anonymous]

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

[kenphamvn]

Hi

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

Output here

[Anonymous]

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