compare rows of a dataset

[#1]

Good morning,

I want to compare rows from my dataset to unbolt my data from particular columns (here B, C and D).

For example, I have the table below :

A     B     C                              D

1     1B     23/03/2017 12:12     344

2     2B     23/03/2017 11:50     20

3     1B     23/03/2017 12:12     344

4     3B     23/03/2017 09:11    32

The expected result would be

- either to delete the row :

A     B     C                              D

1     1B     23/03/2017 12:12     344

2     2B     23/03/2017 11:50     20

4     3B     23/03/2017 09:11    32

- or to add a new column E like this :

A     B     C                              D       E

1     1B     23/03/2017 12:12     344     1

2     2B     23/03/2017 11:50     20       1

3     1B     23/03/2017 12:12     344     0

4     3B     23/03/2017 09:11    32        1

I don't know if I need to do it directly in the script or not.

I wrote something in SQL but I don't know how to adapt it to Qlik Sense or if it is really correct.

select * from t1

where t1.A in (select A from t2

                    where t2.B = t1.B

                    and t2.C=t1.C

                    and t2.D=t1.D)

I hope I was clear and someone could help me.

Thank you,

Paloma

[OmarBenSalem]

Hi Mura,

Do as follow:

Load (min(A)) as A, B,C,D Group By B,C,D;

* Inline [

A,    B,     C,              D

1,     1B,     23/03/2017 12:12 ,    344

2,    2B,     23/03/2017 11:50 ,    20

3,     1B,     23/03/2017 12:12,     344

4,     3B,     23/03/2017 09:11,    32

];

result:

[sunny_talwar]

May be try this

Table:

LOAD *,

  AutoNumber(B&C&D) as Key;

LOAD * INLINE [

    A, B, C, D

    1, 1B, 23/03/2017 12:12, 344

    2, 2B, 23/03/2017 11:50, 20

    3, 1B, 23/03/2017 12:12, 344

    4, 3B, 23/03/2017 09:11, 32

];

FinalTable:

LOAD *,

  If(Key = Previous(Key), 0, 1) as E

Resident Table

Order By Key, A;

DROP Table Table;

[#4]

Hi,

Thank you for your answer. The data will be loaded directly to Qlik from the server (where are stored the data). Do the code will work from this configuration?

Also, sometimes the hour of the date can be different, for example :

  1, 1B, 23/03/2017 12:12, 344

    3, 1B, 23/03/2017 18:00, 344

How can I keep the date the most recent (put 0 for the oldest date and 1 to the most recent), knowing this variable is a part of the key ?

Thank you,

Paloma

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD *,

  AutoNumber(B&D) as Key;

LOAD * INLINE [

    A, B, C, D

    1, 1B, 23/03/2017 12:12, 344

    2, 2B, 23/03/2017 11:50, 20

    3, 1B, 23/03/2017 18:00, 344

    4, 3B, 23/03/2017 09:11, 32

];

FinalTable:

LOAD *,

  If(Key = Previous(Key), 0, 1) as E

Resident Table

Order By Key, C desc;

DROP Table Table;

[#6]

Thank you for your answer, it works now.

[sunny_talwar]

Great, please close this thread if you got what you wanted.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny