scripting the table

[Anonymous]

Hi,

I need to solve the following task.

There are 3 fields im my table:

-index (two possible values - 1 or nothing)

-category

-vaue

I need to set the following rule for each category: if in any line of a table a certain category value has got a corresponding index=1, then it should be assigned index=1 in every line of the table.

How can I write the script in order to get the needed result?

Before

Index	Category	Value
1	A	15
	A	12
	B	147
	C	52
	A	39
	C	7
1	B	45
	C	1

After

Index	Category	Value
1	A	15
1	A	12
1	B	147
	C	52
1	A	39
	C	7
1	B	45
	C	1

Thank you in advance,

Larisa

[swuehl]

There are different approaches, maybe like

INPUT:

LOAD Index,

     Category,

     Value

FROM

[https://community.qlik.com/thread/220244]

(html, codepage is 1252, embedded labels, table is @1);

RESULT:

LOAD Category,

  Value

RESIDENT INPUT;

LEFT JOIN

LOAD Max(Index) as Index,

  Category

RESIDENT INPUT

GROUP BY Category;

DROP TABLE INPUT;

Another one could use a Peek() in a table ordered by Category and Index desc.

[sunny_talwar]

May be like this:

Table:

LOAD Index,

     Category,

     Value

FROM

[https://community.qlik.com/thread/220244]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)

LOAD Category,

  Index as Index1

Resident Table

Where Index = 1;

DROP Field Index;

RENAME Field Index1 to Index;

[Anonymous]

Is it necessary to group it by category?

[sunny_talwar · Accepted solution]

Or may be using ApplyMap:

MappingTable:

Mapping

LOAD Distinct Category,

  Index

FROM

[https://community.qlik.com/thread/220244]

(html, codepage is 1252, embedded labels, table is @1)

Where Index = 1;

Table:

LOAD ApplyMap('MappingTable', Category, Null()) as Index,

  Category,

  Value

FROM

[https://community.qlik.com/thread/220244]

(html, codepage is 1252, embedded labels, table is @1);

[MindaugasBacius]

map_index:
mapping LOAD Distinct Category, 
     Index
FROM
[https://community.qlik.com/thread/220244]
(html, codepage is 1257, embedded labels, table is @1)
where Index = 1;

Table:
LOAD Category, 
     Value,
     ApplyMap('map_index', Category, null()) as Index
FROM
[https://community.qlik.com/thread/220244]
(html, codepage is 1257, embedded labels, table is @1);

Try this one.

[Anonymous]

Thanks everyone for your ideas!

I think that applying mapping is the most obvious way to do it.