Hi!
I have got three exported xlsx-tables from filemaker pro. The aim is to review the leadtimes and errors of each section.
I could not get my head around the data and the "auto generated section" in qlik did not provide the any good data structure.
I recreated test data and uploaded it to a test sql database just to manage the data.
This SQL- query gives me the appropriate output:
with x as
(
select p.BNUMBER,
s.ENTITY,
bid,
STATUS,
STATUSTIMESTAMP,STATUSOLD, STATUSTIMESTAMPOLD,
extract(hour from (STATUSTIMESTAMP - STATUSTIMESTAMPOLD)) as timeDiff,
e.sum_errors,
e.SECTION,
e.id_dok from status_log s
join protocols p
on p.bid=s.bid
join
(select SECTION, count(id_dok) as sum_errors, id_dok from errors
where SECTION is not null
group by id_dok, SECTION) e
on e.id_dok=p.id
order by STATUSTIMESTAMPOLD, bid
)
select EB003_SECTION as section,
sum(timediff) as Leadtimes,
sum_errors Errors from x
group by SECTION, sum_errors
SECTION LEADTIMES ERRORS
Section 1 | 65 | 54 |
Section 2 | 16 | 1 |
Section 3 | 95 | 4 |
ODBC via filemaker seems like a hassle. From another thread:
"BTW, another way to go from FileMaker to Qlikview is to export the tables in FM as excel. This works faster for me. As the ODBC couldn't be automated, in the end this excel export is as good as a manual ODBC import."
Do you have any proper tips how to rewrite this query for LOAD statements.