topic Re: Table based on two condition (with Muiltiple ID) in QlikView

Table based on two condition (with Muiltiple ID)

Anonymous — Fri, 16 Aug 2013 16:42:43 GMT

I have one table like:

ID, Type, Status

AAA1 After B

AAA1 Before A

AAA2 After A

AAA2 Before A

BBB1 After B

BBB1 Before A

BBB3 After A

BBB3 Before C

DDD5 After A

DDD5 Before C

DDD4 After A

DDD4 Before B

FFF2 After A

FFF2 Before A

GGG5 After A

GGG5 Before C

I want to create a table with a function that compares the field "Status" when Type is like "Before" and "After" and just appears ID once. Like that:

ID, Status Before, Status After, Changed?

AAAA1 A B YES

BBB1 A B YES

BBB3 C A YES

DDD4 B A YES

DDD5 C A YES

FFF2 A A NO

GGG5 C A YES

How could I make this?

Thak you!

Re: Table based on two condition (with Muiltiple ID)

Gysbert_Wassenaar — Fri, 16 Aug 2013 16:54:48 GMT

See attached example.

Re: Table based on two condition (with Muiltiple ID)

Fri, 16 Aug 2013 16:56:06 GMT

Step 1:

One new table with distinct ID.

Table:

LOAD* distinct ID

Resident YourTable;

Step 2:

Load the Status= After:

Left Join (Table)

Load

Status as [Status After]

Where Type='After';

Step 3:

Same for Before:

Left Join (Table)

Load

Status as [Status Before]

Where Type='Before';

Step 4:

Compare the two values and create the new 'Changed' field. Also, clean the non interesting rows (nulls?)

Re: Table based on two condition (with Muiltiple ID)

Anonymous — Fri, 16 Aug 2013 16:59:34 GMT

Hi Iosu!

Thak you for your help!

Regards,

Debora

Re: Table based on two condition (with Muiltiple ID)

Anonymous — Fri, 16 Aug 2013 17:01:23 GMT

Hi Gysbert!

Perfect and simple solution.

Thank you for your help!

Regards,

Debora