Count of 1

[#1]

I'd like to add a column in my first load for [Emp Count] = 1, similar to what I am loading in the concatenated file.  All it is, is a column that has a "1" in it.  How do I do this? 

CONNECT32 TO compositesw (XUserId is RAcQbXFMTLaGHMB, XPassword is UYPLRIVOBDbGWXVNNDMCDEC);
LOAD
  "HOME_DEPARTMENT" as DEPT_ID,
    "QC_EMP_NUM" as [Emp Num],
    "EMP_FULLNAME" as [Employee Name],
    "WORK_WEEK_ENDING_DATE" as weekkey,
    "TOTAL_OT1_HOURS" as [Total Hours];
SQL SELECT *
FROM ENG."QTIME_SOURCE"."QTIME_SOURCE_13MONTH"
where WORK_WEEK_ENDING_DATE = '2014-01-24 00:00:00'
and  TOTAL_OT1_HOURS > 0;

Concatenate 
LOAD 
  Dept# as DEPT_ID, 
     [Emp Num],
     [Emp Count],
     [Employee Name], 
     [Work Week Ending],
     [Work Week Ending] as weekkey,
     [Total Hours]
FROM

(ooxml, embedded labels, table is Sheet1);

[swuehl]

CONNECT32 TO compositesw (XUserId is RAcQbXFMTLaGHMB, XPassword is UYPLRIVOBDbGWXVNNDMCDEC);
LOAD
  "HOME_DEPARTMENT" as DEPT_ID,
    "QC_EMP_NUM" as [Emp Num],

     1 as [Emp Count],
    "EMP_FULLNAME" as [Employee Name],
    "WORK_WEEK_ENDING_DATE" as weekkey,
    "TOTAL_OT1_HOURS" as [Total Hours];
SQL SELECT *
FROM ENG."QTIME_SOURCE"."QTIME_SOURCE_13MONTH"
where WORK_WEEK_ENDING_DATE = '2014-01-24 00:00:00'
and  TOTAL_OT1_HOURS > 0;

[rustyfishbones]

Hannah,

Is a LEFT JOIN not a better solution than CONCATENATE?

Regards

Alan

[#4]

I dont know...I am not very knowledgeable about the joins.  My ultimate goal is to save the data in a single qvd (archived file) and update the new data every week.  It's a labor report, so I need to go to the database and get the last month's timecards in case there was a change or late submission.  Any thoughts on that?  The OT report file is an excel file that contains the history (since the users want a 2 year rolling comparison).

[rustyfishbones]

Just replace where you have CONCATENATE with LEFT JOIN and it should work.

[rustyfishbones]

I would need to see the Data if was to try help.

However, I am assuming the timecards will contain [Emp Num], you can use that field to JOIN with the main Data

Qlikview will automatically Join tables as long as there is only one common field linking to each

[#7]

I tried that and it's still only bringing in one tables data:(  I can see at least that it's bringing in the records from the other table when I reload it (it wasn't doing that before):

ODBC CONNECT32 TO compositesw (XUserId is RAcQbXFMTLaGHMB, XPassword is UYPLRIVOBDbGWXVNNDMCDEC);
LOAD
  "HOME_DEPARTMENT" as DEPT_ID,
    "QC_EMP_NUM" as [Emp Num],
         1 as [Emp Count],
    "EMP_FULLNAME" as [Employee Name],
     "WORK_WEEK_ENDING_DATE" as  [Work Week Ending],
    "WORK_WEEK_ENDING_DATE" as weekkey,
    "TOTAL_OT1_HOURS" as [Total Hours];
SQL SELECT *
FROM ENG."QTIME_SOURCE"."QTIME_SOURCE_13MONTH"
where WORK_WEEK_ENDING_DATE = '2014-01-24 00:00:00'
and  TOTAL_OT1_HOURS > 0;

left join
LOAD 
  Dept# as DEPT_ID, 
     [Emp Num],
     [Emp Count],
     [Employee Name], 
     [Work Week Ending],
     [Work Week Ending] as weekkey,
     [Total Hours]
FROM

(ooxml, embedded labels, table is Sheet1);

[#8]

yes this is true.  do you have a suggestion for joining the Emp Num?

[rustyfishbones]

Actually I have Just noticed, Concatenate will work!

you just need to add 1 AS [Emp Count] in both tables and then Concatenate will work.

Sorry for the confusion

[#10]

I have [Emp Count] in the second table (the excel sheet already has 1 for this data).  So, all of my fields match.  It still didn't work:(