Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
amiroh81
Creator
Creator

join between 2 Queries with like condition

Hello everyone,

I try to join between SQL query and inline when the join I want to execute will be provided that the inline data will be like the number in SQL.

For example:

  sql:

typecount
1233311350
4257235
6445214444
8752211

32

   inline

group typegroup number
1AAA
42BBB
6CCC
8752DDD

   join table

typecountgroup number
1233311350AAA
4257235BBB
6445214444CCC
875221132DDD

Thanks in advance

1 Solution

Accepted Solutions
isorinrusu
Partner
Partner

Hi Amir,

One way to solve it is through an iteration on each "group type" value  and using the Wildmatch or SubStringCount functions, building it step by step.

For example:

for each vGroupType in FieldValueList('group type')

     Grouping:

     Load type,

              count,

               '$(vGroupType)'     as GroupType

     Resident sql where wildmatch(type,'$(vGroupType)*')>0;

next;


Drop table sql;


Left Join (Grouping)

Load "group type"     as GroupType,

        "group number"

Resident inline;


Drop table inline;


That should work.


Regards,

Sorin.

    

View solution in original post

3 Replies
isorinrusu
Partner
Partner

Hi Amir,

One way to solve it is through an iteration on each "group type" value  and using the Wildmatch or SubStringCount functions, building it step by step.

For example:

for each vGroupType in FieldValueList('group type')

     Grouping:

     Load type,

              count,

               '$(vGroupType)'     as GroupType

     Resident sql where wildmatch(type,'$(vGroupType)*')>0;

next;


Drop table sql;


Left Join (Grouping)

Load "group type"     as GroupType,

        "group number"

Resident inline;


Drop table inline;


That should work.


Regards,

Sorin.

    

View solution in original post

amiroh81
Creator
Creator
Author

thank you very much!

amiroh81
Creator
Creator
Author

Hi ,

While I was trying to implement the solution (working properly) I encountered another problem.

 

typecount
1233311350
4257235
1244444444
8752211

32

  

group typegroup number
1AAA
42BBB
1244CCC
8752DDD

  

typecountgroup number
1233311350AAA
4257235BBB
1244444444CCC
875221132

DDD

You can see that the prefix of the first and third lines is the same prefix but it is about two different group number.

Is it possible that the join between the two queries can be performed according to the correct prefix?

Thanks