Create key field and join table

[didierodayo]

Hello experts,

I need to JOIN the 2 tables below using the Batch_ID from TABLE 1  and left join TABLE 2.

The problem as you can see below is that TABLE2 doesn't have Batch_id and I need to make it using CASE 1 AND CASE2.

CASE1 can be from 1 character to 7 characters maximum

CASE2 can be from 1 character to 2 characters maximum

BATCH_ID IS A combination of CASE1 and CASE2 (always 9 characters) if not enough ZEROS are added.

I need to create a new field on table2 using CASE 1 and CASE2 to form the BATCH_Id (11 CHARACTERS). It is all numbers.

                             TABLE1

Batch_ID	SEAT_NM
000345601	1
897525605	2
000525699	3
000003110	4

                         TABLE2

CASE1	CASE2	Student_ID
3456	1	588
8975256	5	687
5256	99	289
31	10	554

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD Batch_ID,

    SEAT_NM

FROM

[https://community.qlik.com/thread/221925]

(html, codepage is 1252, embedded labels, table is @1);

Table2:

LOAD Num(CASE1, '0000000') & Num(CASE2, '00') as Batch_ID,

  CASE1,

    CASE2,

    Student_ID

FROM

[https://community.qlik.com/thread/221925]

(html, codepage is 1252, embedded labels, table is @2);

[didierodayo]

Thanks a lot Sunny. You are the best!!!