Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have data in the following formats:-
There are roughly 50 ATB codes
ATB Code | Description | Compatible CIB Codes |
---|---|---|
1 | Central | 0111-6423, 7012-7018, 6603 |
2 | Local | 0111-6423, 7012-7018 |
+
There are roughly 500 CIB Codes
CIB Codes | Desc |
---|---|
0111 | Desc 1 |
0112 | Desc 2 |
0123 | Desc 3 |
0124 | Desc 4 |
I want to create a row based table of acceptable ATB and CIB combinations that would end up in this format:-
ATB Code | CIB Codes |
---|---|
1 | 0111 |
1 | 0112 |
1 | 0123 |
1 | 0124 |
Is this doable using Qlikview? If so what script commands would one use?
Thanks
Stuart
Oh sorry , I got you problem now let me work on this
left join would work better with what you need, since I am assuming that you want ATB Code 1 and ATB Code 2 to be associated with the CIB Codes regardless if they were already populated or no and mapping will only add the first value found.
Are all your Compatible ATB Codes in that format ? If so then you will need a for to create the data for each range
This is an interesting one
The code has correctly split out the ranges onto lines but it needs to go one step further.
So instead of showing:-
We would see:-
ok then instead of
Subfield([Compatible ATB Codes], ',', $(n)) as CompatibleATBCode
Use this
Subfield(Subfield([Compatible ATB Codes], ',', $(n)), '-', 1) as CompatibleATBCode
Yes it is possible:
T1:
LOAD * INLINE [
ATBCode , CompCIBCode
1, '01-20, 30-39'
2, '10-15, 20-29'
];
T2:
Load ATBCode,
trim(SubField(CompCIBCode,',')) as CompCIBCode1
Resident T1;
DROP Table T1;
T3:
load ATBCode,
SubField(CompCIBCode1,'-',1) as Start, subField(CompCIBCode1,'-',2) as End
Resident T2;
DROP table T2;
for i=0 to NoOfRows('T3')
let vStart=num#(Peek('Start',$(i),'T3'),'#');
let vEnd=num#(peek('End',$(i),'T3'),'#');
Let vATBCode=Peek('ATBCode',$(i),'T3');
for j= '$(vStart)' to '$(vEnd)'
T4:
load '$(vATBCode)' as ATBCode,
'$(j)' as CIBCode
AutoGenerate 1;
next;
next;
drop Table T3;
Hi Stuart,
I think the attached should do what you are looking for.
Hope that helps
Joe
Joe's use of interval match is a good idea and it was something I overlooked
you can use this if you want to use a for
CIB:
LOAD NUM(CIB,0000) AS CIB,
F2
FROM
[..\sample data set.xlsx]
(ooxml, embedded labels, table is [CIB CODES]);
TEMP:
LOAD ATB,
[Compatible ATB Codes]
FROM
[..\sample data set.xlsx]
(ooxml, embedded labels, table is [ATB Codes]);
let vATBValuesCount= FieldValueCount('ATB');
for ATBCounter = 1 to $(vATBValuesCount)
let vATBCode = FieldValue('Compatible ATB Codes',$(ATBCounter));
for vATBCodeCounter = 1 to (SubStringCount(FieldValue('Compatible ATB Codes',$(ATBCounter)),','))+1
let vATBValue = SubField('$(vATBCode)',',',$(vATBCodeCounter));
if index($(vATBValue), '-') > 0 then
for RangerCounter = SubField('$(vATBValue)','-',1) to SubField('$(vATBValue)','-',2);
ATB:
load
ATB,
NUM($(RangerCounter),0000) as CIB
Resident TEMP;
next
ELSE
ATB:
load
ATB,
NUM($(RangerCounter),0000) as CIB
Resident TEMP;
ENDIF
NEXT
NEXT
LEFT JOIN(ATB)
LOAD
*
Resident CIB;
DROP TABLES CIB;
multiple ways to do this really Ramon no worries both ways work
It does - thanks very much to you and everyone else for the help. Such a great community !