topic ? in QlikView

?

xyz1 — Sun, 14 Apr 2019 14:57:44 GMT

Re: categorize values.

ahaahaaha — Fri, 09 Jun 2017 06:53:45 GMT

Hi,

Two questions.

. . .

Output:

LOAD *

RESIDENT source

WHERE MatriAccounts =-1 AND MatriculeAccounts >0;

DROP table source;

It is right? If the field value is empty, it can not be greater than zero (8 of its signs). On idea, [Output] table should not contain a records.

2. Only these two values should be in the resulting table? What is the sign of the selection?

Regards,

Andrey

?

xyz1 — Sun, 14 Apr 2019 14:57:21 GMT

Re: categorize values.

ahaahaaha — Fri, 09 Jun 2017 08:17:42 GMT

Hi,

If I understood you correctly, may be like this

LOAD*Inline

[Pre-W2K Name-

0101443Z

0101444A

01015693r

0102101P

0102120P

0102196P

0102197R

0102213L

0102218T

ADM_9929390X

ADM_9929500R

ADM_9929503W

ADM_9929560W

ADM_9929626g

ADM_ACCOUNT_RESERVE

adm_arumtec

ADM_AUTOCHECKDMZ

ADM_BIPS

ADM_CHECK_DMZ

ADM_CONTPAQI_PROD

ADM_CONTRACTORPBX

ADM_CONTRACTORTIMECL

ADM_COPERNIC

]

Where Left([Pre-W2K Name-], 4) = 'ADM_' And RangeSum(Num(Mid([Pre-W2K Name-], 5, 6)))>0;

Look attached file

?

xyz1 — Sun, 14 Apr 2019 14:56:42 GMT

Re: categorize values.

ahaahaaha — Fri, 09 Jun 2017 11:52:08 GMT

The fastest way is instead

source:

LOAD *,

IsNum(left("Pre-W2K Name",7)) as MatriAccounts,

Match(Mid("Pre-W2K Name",8), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm',

'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z',

'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',

'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' )

as MatriculeAccounts;

LOAD

"Pre-W2K Name"

FROM [lib://AD Dashboard/AD-DATA-01.06.2017.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Output:

LOAD *

RESIDENT source

WHERE MatriAccounts =-1 AND MatriculeAccounts >0;

DROP table source;

May be like this

LOAD

"Pre-W2K Name"

FROM [lib://AD Dashboard/AD-DATA-01.06.2017.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where Left([Pre-W2K Name-], 4) = 'ADM_' And RangeSum(Num(Mid([Pre-W2K Name-], 5, 6)))>0;

It all depends on what you want to get in the first table and what in the [Output] table, as well as how they should be related (if they should). Only by code is difficult to determine.

May be fragment of source data and what you wont to get in result?