Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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.
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;
Is it necessary to group it by category?
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);
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.
Thanks everyone for your ideas!
I think that applying mapping is the most obvious way to do it.