topic Re: sort data by apply map in QlikView

sort data by apply map

akpofureenughwu — Mon, 21 Aug 2017 14:15:07 GMT

Hello everyone,

I have two data in the form

Data Table

rid	Name	Class	vbatchId
1	John Lee	SS3	Asdioe12d
2	Katty Jenny	SS1	dfoemd1340d
3	Jhon Brown	SS1	Dfthgkdwo45ty
4	Owen Green	DD6	AFgeiwo56h

Verification Table

rid	vbatchId	Status
1	Asdioe12d	True
2	dfoemd1340d	False
3	Dfthgkdwo45ty	False
4	AFgeiwo56h	True

The design I am to visualize only data that their vbatchId status is equal to 'True'

I am trying using ApplyMap but it's not working. I need help

Re: sort data by apply map

Anil_Babu_Samineni — Mon, 21 Aug 2017 14:18:17 GMT

Try to show expected result from this table or post your Applymap code to better understand

Re: sort data by apply map

akpofureenughwu — Mon, 21 Aug 2017 14:25:19 GMT

rid	Name	Class	vBatchId
1	John Lee	SS3	Asdioe12d
2	Owen Green	DD6	AFgeiwo56h

since their vBatchId hold 'True in the verification table

Regards

Re: sort data by apply map

simotrab — Mon, 21 Aug 2017 14:27:25 GMT

Hi,

applymap applies a mapping on data, the result should be something like:

Verification Table

rid	vbatchId	Status
1	Asdioe12d	True
2	dfoemd1340d	True
3	Dfthgkdwo45ty	True
4	AFgeiwo56h	True

ApplyMap ‒ QlikView

If you want to visualize only the True status you ought to add a Where clause in your load script.

However, if you need a mapping it should be something like:

Mapping:

Mapping Load Old, New Inline

[Old, New,

'False', 'True'

];

Load

rid

,vbatchid

,(ApplyMap('Mapping', Status) as Status

;

sql select

from Verification Table;

Re: sort data by apply map

akpofureenughwu — Mon, 21 Aug 2017 14:29:27 GMT

Simone.. Thanks but you didn't get the verification table well.. Please look at it carefully

Re: sort data by apply map

simotrab — Mon, 21 Aug 2017 14:42:02 GMT

Thanks, my mistake, maybe this, joining the two table.

If you want only the True on the data table, looking the verification table, you could do this:

load

d.rid

,d.name

,d.class

,d.vBatchid

;

sql select

from data table d

join Verification table v on d.rid=v.rid

where Status = 'True'

;

Re: sort data by apply map

antoniotiman — Mon, 21 Aug 2017 14:48:21 GMT

Hi Akpofure,

maybe thsi

LOAD rid,
Name,
Class,
vbatchId
FROM
"https://community.qlik.com/message/1329296"
(html, codepage is 1252, embedded labels, table is @1);
Inner Join LOAD rid,
vbatchId,
Status
FROM
"https://community.qlik.com/message/1329296"
(html, codepage is 1252, embedded labels, table is @2)
Where Status = 'True';

Regards,

Antonio

Re: sort data by apply map

vishsaggi — Mon, 21 Aug 2017 15:02:04 GMT

May be this?

As you have vbatchId as common field in both the tables auto concatenation happens then using straight table try like below:

Table1:

LOAD rid,

Name,

Class,

vbatchId

FROM

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

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

Table2:

LOAD rid AS Ver_rid, // Rename this field so that the tables can connect using vbatchId field.

vbatchId,

Status

FROM

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

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

Using straight table

Add dimensions: vbatchId, Name

Expression : Only({< Status = {'True'} >}Status)

You will get below: