topic scripting the table in QlikView

scripting the table

Anonymous — Thu, 09 Jun 2016 13:46:48 GMT

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

Re: scripting the table

swuehl — Thu, 09 Jun 2016 13:53:03 GMT

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.

Re: scripting the table

sunny_talwar — Thu, 09 Jun 2016 13:57:27 GMT

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;

Re: scripting the table

Anonymous — Thu, 09 Jun 2016 13:59:24 GMT

Is it necessary to group it by category?

Re: scripting the table

sunny_talwar — Thu, 09 Jun 2016 14:00:09 GMT

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);

Re: scripting the table

MindaugasBacius — Thu, 09 Jun 2016 14:01:24 GMT

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.

Re: scripting the table

Anonymous — Thu, 09 Jun 2016 14:05:15 GMT

Thanks everyone for your ideas!

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