join between 2 Queries with like condition

[amiroh81]

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:

type	count
12333113	50
425723	5
644521	4444
8752211	32

   inline

group type	group number
1	AAA
42	BBB
6	CCC
8752	DDD

   join table

type	count	group number
12333113	50	AAA
425723	5	BBB
644521	4444	CCC
8752211	32	DDD

Thanks in advance

[isorinrusu · Accepted solution]

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.

[amiroh81]

thank you very much!

[amiroh81]

Hi ,

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

type	count
12333113	50
425723	5
124444	4444
8752211	32

group type	group number
1	AAA
42	BBB
1244	CCC
8752	DDD

type	count	group number
12333113	50	AAA
425723	5	BBB
124444	4444	CCC
8752211	32	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