Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Reproducing SQL-type statement in Script Load

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

11 Replies
flipside
Valued Contributor II

Re: Reproducing SQL-type statement in Script Load

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

Not applicable

Re: Reproducing SQL-type statement in Script Load

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.

Not applicable

Re: Reproducing SQL-type statement in Script Load

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!

flipside
Valued Contributor II

Re: Reproducing SQL-type statement in Script Load

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

Not applicable

Re: Reproducing SQL-type statement in Script Load

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;

datastructure.png

flipside
Valued Contributor II

Re: Reproducing SQL-type statement in Script Load

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

Not applicable

Re: Reproducing SQL-type statement in Script Load

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.

flipside
Valued Contributor II

Re: Reproducing SQL-type statement in Script Load

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

Not applicable

Re: Reproducing SQL-type statement in Script Load

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.