Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Do u want to create buckets of account numbers?
no i want to give a rang EX :
A : P12000 to P12100
like that
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
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
Hi There,
I'm trying to map a range of account numbers to a group.
For example:
Account Groups Exaample:
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.
Plese share a sample of both the spread sheets.
Hi Rupali
See the Attached File
Is this what you want?
Regards
Aviral Nag
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,
*;
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
