Help with data model

[daniel_kusiak]

Hello Community,

I was hoping that you guys can help me with my problem

I have two tables T1 and T2.

In T2 i have two columns Acc and Amount. In T1 i have filter and description. What i want to do is that every Acc from T2 that matches filter from T1 will be given a description from T1. For example every Acc that matches filter 100* will be given description D1, every Acc that matches filter 200* will be given description D2. Note that I have more complex filter like 300??-1*. I hope that it is possible to do this by some kind of data transformation in data model. The filter will be always some kind of number with * and ? - like in wildmatch function.

Since there will be many more filters i dont what to use where clause.

Thanks in advance.

Daniel.

[ziadm]

Hi Daniel

Consider the pick and match function to build a wild expression mapping.  This script was developed by Rob wunderlich

!

[ziadm]

Sorry missed the attachment

[ziadm]

This is what you get

[daniel_kusiak]

Hi ziadmohd1 and thank you for your reply.

The solution you have given me works great, but unfortunately I haven't consider all the possibilities.

I think that pick wildmatch is working fine untill it finds first match. But what if one Filter can match more than one description? Please look at the attached excel to see what i'm trying to achive.

[khalander]

Hi,

try this

T1:

Mapping load

left(filter,3) as filter,

Description

from t1;

T2:

load

Accn,

Amount,

Applymap('T1',Left(Accn,3),'NA') as Description

from t2;

[CELAMBARASAN · Accepted solution]

May be you can try with the below script

Acc:
LOAD RowNo() as Row,
  Acc, 
     Amount
FROM
t2.xlsx
(ooxml, embedded labels, table is Arkusz1);


Filter:
LOAD Filter, 
     Description
FROM
t1.xlsx
(ooxml, embedded labels, table is Arkusz1);
AccDesc:
Load '' as Description AutoGenerate 0;


LET vNoOfRows = NoOfRows('Filter');
//EXIT Script;
for i=0 to $(vNoOfRows)
  LET vFilter = Peek('Filter', i, 'Filter');
  LET vDescription = Peek('Description', i, 'Filter');
  Concatenate(AccDesc)
  LOAD
  *
  ,'$(vDescription)' as Description
  ,Row as ConvertedRow
  Resident Acc
  Where WildMatch(Acc, '$(vFilter)') ;
NEXT i;


Concatenate(AccDesc)
LOAD * 
Resident Acc
Where Not Exists(ConvertedRow,Row);
DROP Table Acc, Filter;
DROP Fields Row, ConvertedRow;

[daniel_kusiak]

Thank you celambarasan. This solution works like charm.