topic Re: show selected case in field in QlikView

show selected case in field

Tue, 22 Jul 2014 11:57:06 GMT

I have i table like this , i want to make a other table according th rule : For each item FILED 1, If it existes number of U, then number of U, elso, number of S

INITIAL TABLE

FIELD 1	FIELD 2	NUMBER
1	S	7
1	U	6
2	S	5
2	U	-
3	S	4
3	U	9

FINAL TABLE

FIELD 1	NUMBER
1	6
2	5
3	9

How to proceed? thanks in advance

Re: show selected case in field

Tue, 22 Jul 2014 13:08:41 GMT

Table1:

LOAD * INLINE [

FIELD 1, FIELD 2, NUMBER

1, S, 7

1, U, 6

2, S, 5

2, U, -

3, S, 4

3, U, 9

];

table2:

load [FIELD 1] as field1,[NUMBER] resident Table1 where [FIELD 2]='U' and NUMBER <>'-';

table3:

load [FIELD 1],[NUMBER] resident Table1 where [FIELD 2]='S' and not Exists(field1,[FIELD 1] );

drop table Table1;

Concatenate (table2) load [FIELD 1] as field1,[NUMBER] resident table3;

drop table table3;

Re: show selected case in field

Tue, 22 Jul 2014 14:05:55 GMT

Thank you very muche David, your solution looks like work, but could you explain me why :

[FIELD 2]='S' and not Exists(field1,[FIELD 1] )

???

Re: show selected case in field

Tue, 22 Jul 2014 15:37:44 GMT

if i have seval column NUMBER : NUMBER 1 , NUMBER 2, NUMBER3 in the initial table, and for the final table, the rule is the same : If it existes number1 of U, then number1 of U, elso, number1 of S ; If it existes number2 of U, then number2 of U, elso, number2 of S ...

how to play?

INITIAL TABLE

FIELD 1	FIELD 2	NUMBER1	NUMBER2	NUMBER3
1	S	7	23	5
1	U	6	56	18
2	S	5	78	20
2	U	-	55	30
3	S	4	44	77
3	U	9	-	-
4	S	5	7	24
4	U	7	-	-

FINAL TABLE

FIELD 1	NUMBER1	NUMBER2	NUMBER3
1	6	56	18
2	5	55	30
3	9	44	77
4	7	7	24

Re: show selected case in field

Wed, 23 Jul 2014 07:42:48 GMT

This script is easier to understand than the one I wrote before,

Table:

LOAD * INLINE [

FIELD 1, FIELD 2, NUMBER1, NUMBER2, NUMBER3

1 ,S ,7 ,23 ,5

1 ,U ,6 ,56 ,18

2 ,S ,5 ,78 ,20

2 ,U ,- ,55 ,30

3 ,S ,4 ,44 ,77

3 ,U ,9 ,- ,-

4 ,S ,5 ,7 ,24

4 ,U ,7 ,- ,-];

Table1:

load [FIELD 1],NUMBER1 as NUMBER1_U, NUMBER2 AS NUMBER2_U, NUMBER3 AS NUMBER3_U resident Table where [FIELD 2]='U';

join load [FIELD 1],NUMBER1 as NUMBER1_S, NUMBER2 AS NUMBER2_S, NUMBER3 AS NUMBER3_S resident Table where [FIELD 2]='S';

drop table Table;

Table2:

load *,

if(NUMBER1_U<>'-',NUMBER1_U,NUMBER1_S) AS NUMBER1,

if(NUMBER2_U<>'-',NUMBER2_U,NUMBER2_S) AS NUMBER2 ,

if(NUMBER3_U<>'-',NUMBER3_U,NUMBER3_S) AS NUMBER3

resident Table1;

DROP Table Table1;

drop fields NUMBER1_U,NUMBER1_S,NUMBER2_U,NUMBER2_S,NUMBER3_U,NUMBER3_S;