topic Re: Help with data model in QlikView

Help with data model

daniel_kusiak — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Help with data model

ziadm — Wed, 01 Mar 2017 17:05:24 GMT

Hi Daniel

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

Re: Help with data model

ziadm — Wed, 01 Mar 2017 17:06:30 GMT

Sorry missed the attachment

Re: Help with data model

ziadm — Wed, 01 Mar 2017 17:08:47 GMT

This is what you get

Re: Help with data model

daniel_kusiak — Thu, 02 Mar 2017 08:40:57 GMT

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.

Re: Help with data model

khalander — Thu, 02 Mar 2017 09:02:21 GMT

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;

Re: Help with data model

CELAMBARASAN — Thu, 02 Mar 2017 09:35:37 GMT

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;

Re: Help with data model

daniel_kusiak — Thu, 02 Mar 2017 10:28:23 GMT

Thank you celambarasan. This solution works like charm.