Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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$]);
Hello Evan.
In debug can not find the field KEY2 from [MAP_BARCODE]:
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$]);
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.
No this field does not exist. Please see the script i sent you and the message.
Can you help me with that ?
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.
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.
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$]?
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.