Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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
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
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