topic Re: give a range having text and number in QlikView

give a range having text and number

Mon, 17 Feb 2014 11:01:49 GMT

Hi All,

I am developing Balance sheet report for FI module. In that I have accounts no having Text and No and i am going to give a range for that account no . So how to give range .QlikTech Admin

Re: give a range having text and number

sundarakumar — Mon, 17 Feb 2014 11:10:24 GMT

Do u want to create buckets of account numbers?

Re: give a range having text and number

Mon, 17 Feb 2014 11:52:50 GMT

no i want to give a rang EX :

A : P12000 to P12100

like that

Re: give a range having text and number

sundarakumar — Mon, 17 Feb 2014 11:59:24 GMT

if the number of data is less you can go for inline mapping,

if u have many rows then u can trim the p in p00001 and convert them to numbers and in edit script create if conditions to map for ranges..

Hope this helps

-Sundar

Re: give a range having text and number

sundarakumar — Mon, 17 Feb 2014 12:08:59 GMT

If it is a five digit number then you can do like

num(right(FIELDNAME,5)) as ID_Filed_name,

Then u can use resident or precdent and group them like

if( ID_Filed_name >0 and ID_Filed_name<=100,1,

if( ID_Filed_name>100 and ID_Filed_name<=200,2,3)) as range

-Hope this helps

-Sundar

Re: give a range having text and number

Mon, 17 Feb 2014 12:15:08 GMT

Hi There,

I'm trying to map a range of account numbers to a group.

For example:

Account Groups Exaample:

AccountCodeStartRange	AccountCodeEndRange	AccountType
R100000	R199999	Group 1
P200000	P299999	Group 2
Q300000	Q350000	Group 3
S350001	S350001	Group 4

Account Data

Account Code Account Name

R100001 Account 1

P100005 Account 2

Q100010 Account 3

S350001 Account xxx

etc..

Although the ranges are wide there may only be 5 or 10 accounts in each range and they are frequently changing.

Can anyone think of an efficient way to map the accounts to the account group ranges in the load script?

ps The Account Groups are loaded from a spreadsheet and the Account Data is loaded from a separate database.

Thank you.

Re: give a range having text and number

sundarakumar — Mon, 17 Feb 2014 12:36:22 GMT

Plese share a sample of both the spread sheets.

Re: Re: give a range having text and number

aveeeeeee7en — Mon, 17 Feb 2014 13:09:46 GMT

Hi Rupali

See the Attached File

Is this what you want?

Regards

Aviral Nag

Re: give a range having text and number

maxgro — Mon, 17 Feb 2014 13:32:14 GMT

just an idea using extended interval match

SCRIPT

Group:

LOAD Group,

Left(Start, 1) as AccountFirstChar,

Mid(Start, 2, 100) as StartNum,

Mid(End, 2, 100) as EndNum

;

LOAD * INLINE [

Start, End, Group

R100000, R199999, Group 1

P200000, P299999, Group 2

Q300000, Q350000, Group 3

S350001, S350001, Group 4

];

Account:

LOAD

Left(AccountCode, 1) as AccountFirstChar,

Mid(AccountCode, 2, 100) as AccountCodeNum,

LOAD * INLINE [

AccountCode, Account

R100001 , Account 1

P100005 , Account 2

Q100010 , Account 3

S350001 , Account xxx

];

left Join IntervalMatch (AccountCodeNum, AccountFirstChar)

LOAD StartNum, EndNum, AccountFirstChar resident Group;

left Join (Account)

LOAD * Resident Group;

DROP Table Group;

RESULT

AccountCode	Account	AccountFirstChar	Group
P100005	Account 2	P
Q100010	Account 3	Q
R100001	Account 1	R	Group 1
S350001	Account xxx	S	Group 4