topic Re: Flags count in QlikView

Flags count

Thu, 10 Jul 2014 09:52:08 GMT

I have two tables 'Table1' with 20 records and 'Table2' with 8 records.

When I left join Table2 to Table1 how do I flag those records in Table1 that DOESNOT match. From the example above I need to flag 12 records. From the below script I can count the flag to get 8 records.

Please see the script I have used below.

Table1:

Load

unique id,

Name

from...

Left join (Table1)

Load

unique id,

type

1 as flag
from...

Re: Flags count

alexandros17 — Thu, 10 Jul 2014 10:04:32 GMT

You cannot because left join from Tab2 on Tab1 "Exclude" records not existing in Tab2 (Tab2 is the starting set of data)

To achive the result you need (the easiest way but there are several ways)

1) join the tables so you have all the combinations among records

2) load again the resulting table in order to flag what you need (something like: if(isnull(fieldfromTab1),'1','0') as flag)

Let me know

Re: Flags count

Thu, 10 Jul 2014 13:12:42 GMT

In this scenario, Left join is not give what do you need.

First do the Join and create the Flag on resident load like below:

Temp:

LOAD

UniqueKey,

UniqueKey AS Flag,

Name

From Table1;

Join (Temp)

LOAD

UniqueKey,

Type

From Table2;

Final_Table:

Noconcatenate

LOAD

UniqueKey,

IF(isNull(Flag) ,1 ,0) AS Flag,

Name.

Type

Resident Temp;

DROP Table Temp;

Re: Flags count

Thu, 10 Jul 2014 13:22:00 GMT

True. But the problem is I have more thn 3 tables and to take the resident after the joins is painful as the tables have millions of records and I get an error saying 'Exceeding allocated memory 2MB' etc...

Table1:

Load

Unique id

field1.1

field1.2

Left join (Table1)

Unique id

field2.1

field2.2

concatenate Table1

Load

Unique id2

field3.1

field3.2

Left join (Table1)

Unique id2

field4.1

field4.2

Re: Flags count

Thu, 10 Jul 2014 14:05:51 GMT

It doesn't matter how many tables you have. As per your script don't do concatenate (concatenate Table1) at first place. Create the temp tables first and finally concatenate the tables.

Please post full table details so community will help more.

Re: Flags count

Thu, 10 Jul 2014 14:15:06 GMT

Try

mapT2a:

Mapping load

unique id,

type

from ....

Table1:

Load

unique id,

Name,

if (tmpType<>0,tmpType) as Type,

if (tmpType=0,0,1) as flag

;

Load

unique id,

Name,

applymap('mapT2a',unique id,0) as tmptype

from...

;

Re: Flags count

maxgro — Thu, 10 Jul 2014 19:08:39 GMT

t1: load rowno() as unique_id, 't1 name ' & rowno() as name AutoGenerate 20;

t2: load rowno() as unique_id, 't2 type ' & rowno() as type AutoGenerate 8;

Map:

Mapping load unique_id, 0 Resident t2;

Right join (t2)

load

applymap('Map', unique_id, 1) as flagnotmatch

Resident t1;

DROP Table t1;