Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Flags count

Hi

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...

1 Solution

Accepted Solutions
MVP
MVP

Re: Flags count

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;

1.png

6 Replies

Re: Flags count

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

Not applicable

Re: Flags count

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;

Not applicable

Re: Flags count

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

Not applicable

Re: Flags count

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.

Not applicable

Re: Flags count

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...

;

MVP
MVP

Re: Flags count

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;

1.png

Community Browser