give a range having text and number

[#1]

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

[sundarakumar]

Do u want to create buckets of account numbers?

[#3]

no i want to give a rang EX :

A : P12000 to P12100

like that

[sundarakumar]

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

[sundarakumar]

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

[#6]

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.

[sundarakumar]

Plese share a sample of both the spread sheets.

[aveeeeeee7en]

Hi Rupali

See the Attached File

Is this what you want?

Regards

Aviral Nag

[maxgro]

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