Create mapping table using substrings

[#1]

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

[avinashelite]

Oh sorry , I got you problem now let me work on this

[ramoncova06]

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

[#4]

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:-

[marcus_malinow]

ok then instead of

Subfield([Compatible ATB Codes], ',', $(n)) as CompatibleATBCode

Use this

Subfield(Subfield([Compatible ATB Codes], ',', $(n)), '-', 1) as CompatibleATBCode

[Anonymous]

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;

[#7 · Accepted solution]

Hi Stuart,

I think the attached should do what you are looking for.

Hope that helps

Joe

[ramoncova06]

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;

[#9]

multiple ways to do this really Ramon no worries both ways work

[#10]

It does - thanks very much to you and everyone else for the help. Such a great community !