topic Re: Reproducing SQL-type statement in Script Load in QlikView

Reproducing SQL-type statement in Script Load

Tue, 15 Jan 2013 14:38:29 GMT

Hi everyone

This is a long shot, but I can't work out how to replicate the following SQL in QlikView. For the purposes of this explaination, I've got two tables: S and R. Currently, I'm loading them in as:

NoConcatenate
NewTable:
Load
          Sum(EQ) as SUMEQ,
          Sum(EUQ) AS SUMEUQ,
DateField,
          Lookup('GroupName','PersonID',FK_Person_ID,'S') as [Group]
Resident R
group by Lookup('GroupName','PersonID',FK_Person_ID,'S'),DateField;

However, I need to introduce a where clause that ensures that the DateField from table R is between two dates from table S. In SQL, I'd do it like the below:

SELECT Sum(r.EQ),Sum(r.EUQ), r.DateField, s.Group
From r
join s on s.personId = r.FK_Person_ID and r.DateField between s.LowerDate and s.UpperDate
group by s.Group, r.DateField

I can't work out how to do such a join in QlikView. Any help is much appreciated; I'm losing sleep over this one!

Cheers

Re: Reproducing SQL-type statement in Script Load

flipside — Tue, 15 Jan 2013 15:24:42 GMT

Hi,

I'm not 100% sure about your data structure but it looks like you need get a full table with DateField, LowerDate & UpperDate so that you can then filter down with a where clause. I'd do it this way ...

NewTable:

Load

Sum(EQ) as SUMEQ,

Sum(EUQ) AS SUMEUQ,

DateField,

FK_Person_ID as pID,

Lookup('GroupName','PersonID',FK_Person_ID,'S') as [Group]

Resident R

group by Lookup('GroupName','PersonID',FK_Person_ID,'S'),DateField,FK_Person_ID;

//You need to re-add the Person ID as you are comparing at that level. Then join in the low and high dates with a //left join ...

left join

Load

DateField,

Group,

Lookup('LowerDate','PersonID',pID,'S') as [DateLow],

Lookup('UpperDate','PersonID',pID,'S') as [DateHigh]

Resident NewTable;

//Once you are at this stage you can then filter it down and re-sum the grouped values at a higher level ...

NewTableGrouped:

Noconcatenate Load

sum(SUMEQ) as SUMEQ,

sum(SUMEUQ) as SUMEUQ,

DateField,

Group

Resident NewTable

where DateField >= DateLow and DateField <= DateHigh

group by Group, DateField;

DROP TABLES NewTable, R, S;

Much easier if you can do this at SQL level !

flipside

Re: Reproducing SQL-type statement in Script Load

Tue, 15 Jan 2013 15:31:51 GMT

Unfortunately, we don't have the space to have the data in a database! I'll have a go now and report back my findings.

Re: Reproducing SQL-type statement in Script Load

Tue, 15 Jan 2013 16:41:59 GMT

Nope, no joy! It isn't grouping the data as I'd expect. Looks like it's counting the groups multiple times rather than filtering out.

Rather than using pseudocode, I'll paste in my actual code if that helps...

NoConcatenate
RosterBreakdown:
Load
          Sum(EQ) as SUMEQ,
          Sum(EUQ) AS SUMEUQ,
          Sum(LQ) as SUMLQ,
          Sum(LUQ) AS SUMLUQ,
          Sum(NQ) as SUMNQ,
          Sum(NUQ) AS SUMNUQ,
          Date(Roster_RosterDate) as RequirementDate,
          Lookup('Self Rostering Group','PersonID',FK_Person_ID,'SelfRostering') as [Self Rostering Group]
Resident Roster
group by Lookup('Self Rostering Group','PersonID',FK_Person_ID,'SelfRostering'),Date(Roster_RosterDate);

Then, transformed to:

RosterBreakdownTemp:
Load
Sum(EQ) as SUMEQ,
Sum(EUQ) AS SUMEUQ,
Sum(LQ) as SUMLQ,
Sum(LUQ) AS SUMLUQ,
Sum(NQ) as SUMNQ,
Sum(NUQ) AS SUMNUQ,
Roster_RosterDate as RequirementDate,
Lookup('Self Rostering Group','PersonID',FK_Person_ID,'SelfRostering') as [Self Rostering Group],
FK_Person_ID as pID
Resident Roster
group by Lookup('Self Rostering Group','PersonID',FK_Person_ID,'SelfRostering'),Roster_RosterDate,FK_Person_ID;
join
Load
     RequirementDate,
     [Self Rostering Group],
     SRStartDate as [DateLow],
     SREndDate as [DateHigh]
Resident SelfRostering;
NoConcatenate
RosterBreakdown:
Load
Sum(SUMEQ) as SUMEQ,
Sum(SUMEUQ) AS SUMEUQ,
Sum(SUMLQ) as SUMLQ,
Sum(SUMLUQ) AS SUMLUQ,
Sum(SUMNQ) as SUMNQ,
Sum(SUMNUQ) AS SUMNUQ,
    RequirementDate,
    [Self Rostering Group]
Resident RosterBreakdownTemp
where RequirementDate >= DateLow and RequirementDate <= DateHigh
group by [Self Rostering Group], RequirementDate;
DROP TABLE RosterBreakdownTemp;

This should reduce my SUMEQ etc figures, but it only increases them!

Re: Reproducing SQL-type statement in Script Load

flipside — Wed, 16 Jan 2013 09:15:51 GMT

I think the problem might be in the join you have used ...

join

Load

RequirementDate,

[Self Rostering Group],

SRStartDate as [DateLow],

SREndDate as [DateHigh]

Resident SelfRostering;

... you need to be joining to itself (RosterBreakdownTemp) in this case and LOOKUP-ing the DateLow & DateHigh values in table SelfRostering - see my previous code. You probably need to ensure that tables Roster & SelfRostering have no common field names - this might be causing errors. It's a bit weird doing it this way when you've come from a SQL background, but it makes sense after you have done it a few times.

flipside

Re: Reproducing SQL-type statement in Script Load

Wed, 16 Jan 2013 10:04:19 GMT

That was a typo I was joining to the RosterBreakdownTemp table. I've put back in the code that you previously suggested to LOOKUP the dates, rather than the code I provided. I also removed the common fields from Roster and SelfRostering.

I can see the logic in the code, but I don't think this type of join will work for me with my data structure. By grouping by the FK_Person_ID, I think I'm duplicating data...

Below is the latest code and I've attached the data structure as an image below. Thanks for the help so far flipside!

NoConcatenate
RosterBreakdownTemp:
Load
          SUM(EQ) as SUMEQ,
          SUM(EUQ) AS SUMEUQ,
          SUM(LQ) as SUMLQ,
          SUM(LUQ) AS SUMLUQ,
          SUM(NQ) as SUMNQ,
          SUM(NUQ) AS SUMNUQ,
          Roster_RosterDate as RequirementDate,
          Lookup('Self Rostering Group','PersonID',FK_Person_ID,'SelfRostering') as [Self Rostering Group],
          FK_Person_ID as pID
Resident Roster
group by Lookup('Self Rostering Group','PersonID',FK_Person_ID,'SelfRostering'),Roster_RosterDate,FK_Person_ID;
join
Load
     RequirementDate,
     [Self Rostering Group],
     Lookup('SRStartDate','PersonID',pID,'SelfRostering') as [DateLow],
     Lookup('SREndDate','PersonID',pID,'SelfRostering') as [DateHigh]
Resident RosterBreakdownTemp;
NoConcatenate
RosterBreakdown:
Load
          Sum(SUMEQ) as SUMEQ,
          Sum(SUMEUQ) AS SUMEUQ,
          Sum(SUMLQ) as SUMLQ,
          Sum(SUMLUQ) AS SUMLUQ,
          Sum(SUMNQ) as SUMNQ,
          Sum(SUMNUQ) AS SUMNUQ,
    RequirementDate,
    [Self Rostering Group]
Resident RosterBreakdownTemp
where RequirementDate >= DateLow and RequirementDate <= DateHigh
group by [Self Rostering Group], RequirementDate;
DROP TABLE RosterBreakdownTemp;

Re: Reproducing SQL-type statement in Script Load

flipside — Wed, 16 Jan 2013 10:30:49 GMT

Hmm, bit tricky without some sample data - can a person be a member of more than one Self Rostering Group? Maybe this needs removing from the first Group By, then adding in as a left join before the existing left join.

Just as another thought, how is the data supplied? Could this not be done through an SQL script connection if you already have working code?

flipside

Re: Reproducing SQL-type statement in Script Load

Wed, 16 Jan 2013 10:47:33 GMT

Yeah a person can be a member of more than one SR Group. I changed the first group by clause to include the pID but left in the SR group for now. Seems to be returning the correct results at a glance. Will update with further information if we've cracked it

New import script:

NoConcatenate
RosterBreakdownTemp:
Load
          SUM(EQ) as SUMEQ,
          SUM(EUQ) AS SUMEUQ,
          SUM(LQ) as SUMLQ,
          SUM(LUQ) AS SUMLUQ,
          SUM(NQ) as SUMNQ,
          SUM(NUQ) AS SUMNUQ,
          Roster_RosterDate as RequirementDate,
          Lookup('Self Rostering Group','PersonID',FK_Person_ID,'SelfRostering') as [Self Rostering Group],
          FK_Person_ID as pID
Resident Roster
group by Lookup('Self Rostering Group','PersonID',FK_Person_ID,'SelfRostering'),Roster_RosterDate,FK_Person_ID;
join
Load
     RequirementDate,
     [Self Rostering Group],
     pID,
     Lookup('SRStartDate','PersonID',pID,'SelfRostering') as [DateLow],
     Lookup('SREndDate','PersonID',pID,'SelfRostering') as [DateHigh]
Resident RosterBreakdownTemp;
NoConcatenate
RosterBreakdown:
Load
          Sum(SUMEQ) as SUMEQ,
          Sum(SUMEUQ) AS SUMEUQ,
          Sum(SUMLQ) as SUMLQ,
          Sum(SUMLUQ) AS SUMLUQ,
          Sum(SUMNQ) as SUMNQ,
          Sum(SUMNUQ) AS SUMNUQ,
    RequirementDate,
    [Self Rostering Group]
Resident RosterBreakdownTemp
where (RequirementDate >= DateLow and RequirementDate <= DateHigh)
or (RequirementDate >= DateLow and DateHigh ='')
group by [Self Rostering Group], RequirementDate;
DROP TABLE RosterBreakdownTemp;

The data is supplied by CSV. We could import it to a SQL database, but it'd still need to be imported into QV anyway so it's possibly 6 and 2 3s.

Re: Reproducing SQL-type statement in Script Load

flipside — Wed, 16 Jan 2013 11:02:47 GMT

Just as a matter of interest, you could use the Microsoft Text ODBC Driver to connect directly to the csv files and execute SQL code against them.

The following code based on your original SQL script seems to work ...

ODBC CONNECT TO {name of odbc connection};

SQL SELECT Sum(r.EQ) as SUMEQ,Sum(r.EUQ) as SUMEUQ, r.DateField, s.[Group]

From [SQLDB_r.csv] as r

INNER join [SQLDB_s.csv] as s on s.personId = r.[FK_Person_ID] WHERE r.DateField BETWEEN s.LowerDate and s.UpperDate

group by s.[Group], r.DateField;

I'd still develop the QV code to suit your needs, but this is useful for checking the code has been converted correctly.

flipside

Re: Reproducing SQL-type statement in Script Load

Wed, 16 Jan 2013 11:08:17 GMT

Now this is very interesting. My only problem is that I've got multiple .csv files to import into each table. Once I've created the tables, can I then perform SQL style commands on them? If so, this might really help me use QV not just for this, but for future dashboards too.

Re: Reproducing SQL-type statement in Script Load

flipside — Wed, 16 Jan 2013 11:17:02 GMT

You can't do SQL commands on resident tables if that's what you mean, but you could Store the tables to a text file then work on that. I can't comment on the performance of this type of connection though as have only played with it. I only use it for quick checking SQL code as I don't have ready access to a SQL or MS Access database.

AFAIK, there are some limitations - limited sub-joins may be one of them, and NULL handling is different.

Re: Reproducing SQL-type statement in Script Load

Wed, 16 Jan 2013 11:27:20 GMT

Ah it was worth a try! The sheer volume of data we've got (hundreds of thousands of rows) would probably rule out storing the data as a text file. I think I've got the correct data now, I'm just trying to remove some records using ANOTHER attempt at something I can do in SQL but not QV!