Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
(
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;
Hannah,
Is a LEFT JOIN not a better solution than CONCATENATE?
Regards
Alan
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).
Just replace where you have CONCATENATE with LEFT JOIN and it should work.
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
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
(
yes this is true. do you have a suggestion for joining the Emp Num?
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
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:(