Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to join a data file with .xls file - only the matching keys

I have created a crosstable to bring in the data from my business system;

     GL-acct #, FiscalYear, FiscalPeriod, ActualAmt

Now I want to use only certain GL-acct# so I created a xls file with GL-acct# and their description.  I am getting every ActualAmt for every FiscalPeriod tied to a couple of the GL-acct# (and description).  Below is the script I'm working with.

CTActual:
CrossTable(actual,aamt, 2)
load
"GL#1" & '-' & "GL#2" AS GLACCT,
CCYY,
TT01,
TT02,
TT03,
TT04,
TT05,
TT06,
TT07,
TT08,
TT09,
TT10,
TT11,
TT12;

SQL SELECT "GL#1",
"GL#2",
CCYY,
TT01,
TT02,
TT03,
TT04,
TT05,
TT06,
TT07,
TT08,
TT09,
TT10,
TT11,
TT12
FROM SAS400C.GLMT where CCYY >= '2011' ;

CTSlsMast:
LOAD SGLACCT,
SDESCRIP
FROM [Sales-accts.xls]
(biff, embedded labels, table is Sheet1$);
Join
Load GLACCT as SGLACCT,
CCYY,
actual,
aamt,
right(actual,2) as Period
Resident CTActual
;

Any help would be greatly appreciated!

5 Replies
Not applicable
Author

Hi,

Use Inner Join insted if Join so we get only Matching values in the both tables.

CTSlsMast:

LOAD SGLACCT,

SDESCRIP

FROM [Sales-accts.xls]

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

Inner Join

Load GLACCT as SGLACCT,

CCYY,

actual,

aamt,

right(actual,2) as Period

Resident CTActual;

Thanks & Regards,

Siri

Not applicable
Author

Thank you Siri for your response.

I've tried that too along with left join and then I only get two of the GL-acct# and nothing for the other 9 (and there is valid data for all GL-acct#s).  I've tried changing the order of the loads, all the different joins and still not the data I'm looking for.

JM

Not applicable
Author

Hi,

As per your comments, The Crosstable not working. Please provide some test data from DB so that I can try it.

Thanks & Regards,

Siri

Not applicable
Author

Hi Siri,

Attached you will find the data as I am loading it, but it comes from the DB on an AS400.  It does seem to load properly because I use some of the other GL # for another chart on my Qlikview.  Below the numbers is a sample of the data in the .xls file I’m trying to join up with.

Thank you for your time and efforts!

JM

Not applicable
Author

I have figured some of this out, the data file I'm bringing in from AS400 has leading zeros which are being dropped there for they do match the acct-numbers in my .xls file.  I need to bring in the leading zeros, maybe as text values.  But so far I'm not able to include the leading zeros.

Any ideas?

JM