11 Replies Latest reply: Jan 16, 2013 6:27 AM by nineworks RSS

    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

        • Re: Reproducing SQL-type statement in Script Load
          Dave Riley

          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

              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

                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
                    Dave Riley

                    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

                        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

                          • Re: Reproducing SQL-type statement in Script Load
                            Dave Riley

                            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

                                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
                                    Dave Riley

                                    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

                                        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
                                            Dave Riley

                                            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.