topic Re: Create mapping table using substrings in QlikView

Create mapping table using substrings

Tue, 31 Mar 2015 14:51:38 GMT

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

Re: Create mapping table using substrings

ramoncova06 — Tue, 31 Mar 2015 14:54:10 GMT

Mapping Functions

The ApplyMap function is used for mapping any expression to a previously loaded mapping table. The syntax is:

applymap('mapname', expr [ , defaultexpr ] )

where:

mapname is the name of a mapping table that has previously been created through the mapping load or the mapping select statement (see Mapping). Its name must be enclosed by single, straight Quotation Marks in Scripting.

expr is the expression, the result of which should be mapped.

defaultexpr is an optional expression which will be used as a default mapping value if the mapping table does not contain a matching value for expr. If no default value is given, the value of expr will be returned as is.

Examples:

// Assume the following mapping table:

map1:

mapping load * inline [

x, y

1, one

2, two

3, three ] ;

ApplyMap ('map1', 2 ) returns ' two'

ApplyMap ('map1', 4 ) returns 4

ApplyMap ('map1', 5, 'xxx') returns 'xxx'

ApplyMap ('map1', 1, 'xxx') returns 'one'

ApplyMap ('map1', 5, null( ) ) returns NULL

ApplyMap ('map1', 3, null( ) ) returns 'three'

The MapSubstring function is used to map parts of any expression to a previously loaded mapping table. The mapping is case sensitive and non-iterative and substrings are mapped from left to right. The syntax is:

mapsubstring('mapname', expr)

This function can be used for mapping parts of any expression on a previously loaded mapping table. The mapping is case sensitive and non-recursive. The substrings are mapped from the left to the right. Mapname is the name of a mapping table previously read by a mapping load or a mapping select statement (see Mapping). The name must be enclosed by single straight quotation marks. Expr is the expression whose result should be mapped by substrings.

Examples:

// Assume the following mapping table:

map1:

mapping load * inline [

x, y

1, <one>

aa, XYZ

x, b ] ;

MapSubstring ('map1', 'A123') returns 'A<one>23'

MapSubstring ('map1', 'baaar') returns 'bXYZar'

MapSubstring ('map1', 'xaa1') returns 'bXYZ<one>'

QlikView 11.20 SR6

Re: Create mapping table using substrings

avinashelite — Tue, 31 Mar 2015 15:01:53 GMT

Hi Stuart,

If you just need to have data from table to be linked with all the data from table 2 then concatenate would do, But what the mapping for 2 here? I.e how you identify 1 showed be linked with 0111,0112,0113 etc ?? and even 2 data show be linked with the 0111,0112,0113 etc? if that is the case then simply concatenate or if their is any condition please specify that mapping condition

Re: Create mapping table using substrings

Tue, 31 Mar 2015 15:04:47 GMT

Avinash that is the part I am struggling with.

We have one text string that says ATB 1 is allowed to use CIB codes 0111-6423.

Within the range 0111-6423 there are actually 290 CIB codes.

The target, for example, is to create the mapping table with 290 lines for ATB 1

Re: Create mapping table using substrings

ramoncova06 — Tue, 31 Mar 2015 15:08:03 GMT

can you share a sample data set, you could probably apply an if condition checking if the mapping exists or not

Re: Create mapping table using substrings

avinashelite — Tue, 31 Mar 2015 15:08:11 GMT

IF you simple need to have create column for the records in CIB code then just use concatenate between those to tables that will create a row for each record in the ATB with a record in the CIB

Hope this solve's the problem

Re: Create mapping table using substrings

Tue, 31 Mar 2015 15:13:24 GMT

have attached a sample dummy data set

Re: Create mapping table using substrings

avinashelite — Tue, 31 Mar 2015 15:15:14 GMT

did you tired with concatenation

Re: Create mapping table using substrings

Tue, 31 Mar 2015 15:17:04 GMT

I cant see how that will work as I need to get the table in the following format:-

ATB Code	CIB Codes
1	0111
1	0112
1	0123
1	0124

Re: Create mapping table using substrings

marcus_malinow — Tue, 31 Mar 2015 15:31:22 GMT

Try this:

_ATB:
LOAD ATB,
[Compatible ATB Codes],
SubStringCount([Compatible ATB Codes], ',') + 1 as Subfields
FROM
[sample data set.xlsx]
(ooxml, embedded labels, table is [ATB Codes]);

MaxSubfields:
LOAD
max(Subfields) as maxSubfields
RESIDENT _ATB;

Let vMaxSubfields = peek('maxSubfields', -1);

DROP TABLE MaxSubfields;

for n = 1 to vMaxSubfields
TRACE $(n);

ATB:
LOAD
ATB,
Subfield([Compatible ATB Codes], ',', $(n)) as CompatibleATBCode,
$(n) as loop
RESIDENT _ATB
WHERE $(n) <= Subfields
;

next n

DROP TABLE _ATB;

Re: Create mapping table using substrings

avinashelite — Tue, 31 Mar 2015 15:33:10 GMT

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

Re: Create mapping table using substrings

ramoncova06 — Tue, 31 Mar 2015 15:37:05 GMT

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

Re: Create mapping table using substrings

Tue, 31 Mar 2015 15:42:01 GMT

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

Re: Create mapping table using substrings

marcus_malinow — Tue, 31 Mar 2015 15:43:44 GMT

ok then instead of

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

Use this

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

Re: Create mapping table using substrings

Anonymous — Tue, 31 Mar 2015 16:15:59 GMT

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;

drop Table T3;

Re: Create mapping table using substrings

Tue, 31 Mar 2015 16:39:00 GMT

Hi Stuart,

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

Hope that helps

Joe

Re: Create mapping table using substrings

ramoncova06 — Tue, 31 Mar 2015 17:26:58 GMT

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,

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;

ELSE

ATB:

load

ATB,

NUM($(RangerCounter),0000) as CIB

Resident TEMP;

ENDIF

LEFT JOIN(ATB)

LOAD

Resident CIB;

DROP TABLES CIB;

Re: Create mapping table using substrings

Wed, 01 Apr 2015 08:01:06 GMT

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

Re: Create mapping table using substrings

Wed, 01 Apr 2015 08:26:13 GMT

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