Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

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

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

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

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

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

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

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

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

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

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

Community Browser