Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

compare rows of a dataset

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

1 Solution

Accepted Solutions
MVP
MVP

Re: compare rows of a dataset

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 Replies
omarbensalem
Esteemed Contributor

Re: compare rows of a dataset

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:

Capture.PNG

Highlighted
MVP
MVP

Re: compare rows of a dataset

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;

Not applicable

Re: compare rows of a dataset

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

MVP
MVP

Re: compare rows of a dataset

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;

Not applicable

Re: compare rows of a dataset

Thank you for your answer, it works now.

MVP
MVP

Re: compare rows of a dataset

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

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny