CONCATENATE TWO DIFFERENT FIELDS FROM TWO DIFFERENT TABLES

[geogou1973]

Hello.

I have the following script

MAIN_TABLE:

LOAD

     KEY,

     STORE_ID,

     DATE_ID,

     POS_NUMBER,

     TICKET_NUMBER,

     AMOUNT,

     QTY,

     MULTI_BARCODE,

     TRANSACTION_TYPE,

     MINUTE_KEY

FROM

[\\10.78.240.65\c$\Program Files\QlikView\PRODUCTION\POCKEE_NEW.QVD]

(qvd);

TABLE_EXCEL1:

LOAD

     date(DATE)&'_'&STORE&'_'&POS_NBR&'_'&TICKET_NBR AS KEY2,

     STORE,

     TICKET_NBR,

     DATE,

     POS_NBR,

     POCKEE_ID

FROM

C:\POCKEE\Pockee_new.xls

(biff, embedded labels, table is [Sheet1$]);

TABLE_EXCEL2:

LOAD

      BARCODE   

FROM

C:\POCKEE\Pockee_new.xls

(biff, embedded labels, table is [Sheet2$]);

how can i concatenate the field KEY2 from table TABLE_EXCEL2 and BARCODE from table TABLE_EXCEL2

like KEY2&'_'BARCODE and named this as KEY in order to join with the table MAIN_TABLE

Is there any solution for this ?

Thank you in advance.

[evan_kurowski]

Hello gou geo

TABLE_EXCEL1:

LOAD

     date(DATE)&'_'&STORE&'_'&POS_NBR&'_'&TICKET_NBR AS KEY2,

     STORE,

     TICKET_NBR,

     DATE,

     POS_NBR,

     POCKEE_ID

FROM

C:\POCKEE\Pockee_new.xls

(biff, embedded labels, table is [Sheet1$]);

TABLE_EXCEL2:

LOAD

      BARCODE  

FROM

C:\POCKEE\Pockee_new.xls

(biff, embedded labels, table is [Sheet2$]);

how can i concatenate the field KEY2 from table TABLE_EXCEL2 and BARCODE from table TABLE_EXCEL2

like KEY2&'_'BARCODE and named this as KEY in order to join with the table MAIN_TABLE

Is there any solution for this ?

Thank you in advance.

//** TABLE_EXCEL2
[MAP_BARCODE]:
MAPPING LOAD
  KEY2,
BARCODE
FROM C:\POCKEE\Pockee_new.xls
(biff, embedded labels, table is [Sheet2$]);


[TABLE_EXCEL1]:
LOAD *,
KEY2 & '_' & ApplyMap('MAP_BARCODE',KEY2) AS KEY;
LOAD
date(DATE)&'_'&STORE&'_'&POS_NBR&'_'&TICKET_NBR AS KEY2,
STORE,
TICKET_NBR,
DATE,
POS_NBR,
POCKEE_ID
FROM C:\POCKEE\Pockee_new.xls
(biff, embedded labels, table is [Sheet1$]);

[geogou1973]

Hello Evan.

In debug can not find the field KEY2 from [MAP_BARCODE]:

[geogou1973]

I have change the script like below and i have the following message

"Generic tables must contain at least 3 fields"

[MAIN_TABLE]:

LOAD KEY,

     STORE_ID,

     DATE_ID,

     POS_NUMBER,

     TICKET_NUMBER,

     AMOUNT,

     QTY,

     MULTI_BARCODE,

     TRANSACTION_TYPE,

     MINUTE_KEY

FROM

[\\10.78.240.65\c$\Program Files\QlikView\PRODUCTION\POCKEE_NEW.QVD]

(qvd);

[MAP_BARCODE]:

MAPPING LOAD

BARCODE

FROM C:\POCKEE\Pockee_new.xls

(biff, embedded labels, table is [Sheet2$]);

[TABLE_EXCEL1]:

LOAD

KEY2& '_' & ApplyMap('MAP_BARCODE',BARCODE) AS KEY;

LOAD

date(DATE)&'_'&STORE&'_'&POS_NBR&'_'&TICKET_NBR AS KEY2,

STORE,

TICKET_NBR,

DATE ,

POS_NBR,

POCKEE_ID

FROM C:\POCKEE\Pockee_new.xls

(biff, embedded labels, table is [Sheet1$]);

[evan_kurowski]

It looked like in your description you mentioned KEY2.TABLE_EXCEL2 existed, so I added it to your load statement. 

gou geo wrote:

Hello.

how can i concatenate the field KEY2 from table TABLE_EXCEL2 and BARCODE from table TABLE_EXCEL2

like KEY2&'_'BARCODE and named this as KEY in order to join with the table MAIN_TABLE

Is there any solution for this ?

Thank you in advance.

[geogou1973]

No this field does not exist. Please see the script i sent you and the message.

Can you help me with that ?

[Digvijay_Singh]

Is it possible to share sample data to confirm the requirements.

I am not sure why Barcode is sitting alone in excel2, is it possible to relate it with any field of excel1 table?

Also are your sure you want to join with main table or you want to concatenate(append data) to main table? A transactions row in different tables are pointing to same transaction?, if not then you may want to concatenate the other tables to main tables.

[geogou1973]

Hello Digvijay.

The senario is to matching the data from the MAIN_TABLE with the TABLE_EXCEL. Before i had the following script because the excel had the information of barcode and was matching with the MAIN_TABLE.

MAIN_TABLE:

LOAD KEY,

    STORE_ID,

    DATE_ID,

    POS_NUMBER,

    TICKET_NUMBER,

    AMOUNT,

    QTY,

    MULTI_BARCODE,

    TRANSACTION_TYPE,

    MINUTE_KEY

FROM

[\\10.78.240.65\c$\Program Files\QlikView\PRODUCTION\POCKEE.QVD]

(qvd);

TABLE_EXCEL:

LOAD date(DATE)&'_'&STORE&'_'&POS_NBR&'_'&TICKET_NBR&'_'&BARCODE AS KEY,

      STORE,

    BARCODE,

    TICKET_NBR,

    DATE,

    POS_NBR,

    NUMBER_COUPONS,

    POCKEE_ID

FROM

C:\POCKEE\Pockee.xls

(biff, embedded labels, table is [Sheet1$]);

In the report i had the following dimensions and expressions and it worked perfect.

The new senario is that the table_excel does not contain the barcode and in the second sheet has the information of all the barcodes that the client has. I try to find a solution how can connect the two sheets and give the same information at the end 1 mathing the data from the main_table and the table_excel and 9 for no matching.

[evan_kurowski]

Hello gou geo,

From your description you mention the original keying system was working fine between your QVD and a single Excel sheet.

But now you are describing that the data elements in your Excel area have been spread out among multiple sheets.

For the new spreadsheet format, on the sheet that holds your barcodes, there has to be some other field on that sheet that forms a connection with your first spreadsheet.

In your scenario, you want to bring information from [Sheet2$] over to [Sheet1$], but that requires at least some sort of keying system that is present in both sheets.  What other fields do you have present in [Sheet2$] besides BARCODE that could be evaluated for establishing an association to [Sheet1$]?

[Digvijay_Singh]

Can you share sample data? Else it would be difficult to suggest work around -

Not sure but try something like below -

TABLE_EXCEL2:

LOAD

      BARCODE as MULTI_BARCODE

FROM

C:\POCKEE\Pockee_new.xls

(biff, embedded labels, table is [Sheet2$]);

Excel2_Join:

JOIN(MAIN_TABLE)

Load MULTI_BARCODE Resident TABLE_EXCEL2;

Excel1_Join:

JOIN(MAIN_TABLE)

Load DATE as DATE_ID,

  STORE as STORE_ID,

  TICKET_NBR as TICKET_NUMBER,

  POS_NBR as POS_NUMBER,

     POCKEE_ID,

     NUMBER_COUPONS,

     POCKEE_ID

Resident TABLE_EXCEL1;

I think Excel2 should directly join with main table as they have bar code field column and after that excel1 can be joined with individual common fields. But without having sample data it is difficult to know if it works or not.