Join and merge columns based on wildmatch

[#1]

Hi Team,

I have 2 tables. One of them is a fact and other is a lookup. Both have 4 columns on which I want to do the lookup and insert the key from the lookup into the fact. I am trying to create a merge string key and then join them based on a wild card match. But the script errors out saying Unknown command. What I am doing wrong?

[Fact]:

LOAD

[FT] as Fact_FT,

  [PT] as Fact_PT,

  [Grp] as Fact_SCG,

  [Sales],

    [FIRM_TYPE]&[PROD_TYPE]&[Grp] as F_key

FROM [lib://Desktop/test.xlsx]

(ooxml, embedded labels, table is Fact);

LEFT JOIN

LOAD

[Group Id],

  [Channel],

  [Channel Sub Code],

  [FT] as GL_FT,

  [PT] as GL_PT,

  [Grp] as GL_SCG,

  [wholeID],

  [Report Name],

    [FT]&[PT]&[Grp] as GL_key

FROM [lib://Desktop/Test.xlsx]

(ooxml, embedded labels, table is [GL]);

on WildMatch(F_key,'*'&GL_key&'*')

I want the Channel and Sub Channel columns to be added in my fact table rows based on the lookup. Not that in the Fact, [FT],[PT], [Grp] will always be populated. But in the Lookup table some of them may be null. That is the reason of doing wildmatch instead of an exact match.

Would really appreciate any help.

[marcus_sommer]

You couldn't join in qlikview on this way - the join will be performed on identical field-names and there is no wildmatch possible. But I think with this you could solve your challenge Don't join - use Applymap instead‌.

- Marcus

[#3]

Thanks Marcus.

Unfortunately the apply map will not work in my case for 2 reasons:

1. I need to insert 1 column in the target table (the unique group key that corresponds the composite key [FT]&[PT]&[Grp] . But the above 3 columns values be always present in Target, but the values may be missing for some columns in the lookup table. Thus the composite key in the lookup table is a 'sub string' of the composite key in fact table. That was the reason for wild match.

QQ: Can apply map be done on wildcard?

[marcus_sommer]

Instead of applymap() you could use mapsubstring() maybe similar to this example: Re: Lookup() Function.

- Marcus

[#5]

Marcus - this did not work out as the substring I am searching may not be contineous. So I might want to search ASDF inside ASXXDF.

Any other thoughts please?

[marcus_sommer]

Honestly, I don't understand what do you try to do and where the problem is and I'm not sure if your join-approach led in the right direction. Have you tried a matching with mapsubstring?

Maybe you will need some intermediate steps, for example by preparing the key-data and replacing some values like NULL with 'NULL' and/or you joined/mapped the tables at first with the exists key-fields and checking/adjusting then the other fields by creating some flags and filtering the data afterwards within a where-clause.

- Marcus