17 Replies Latest reply: Aug 12, 2011 12:31 AM by brian123 RSS

    Minimum Values

    MCPL MCPL

      Hi,

       

      I am loading a qvd file and want only the minimum date values for each client to appear.

      Sample data below:

       

      untitled.bmp

       

      Notice there are multiple values for each PERSON_ID field.  I only want the first date (from the CI_START_DT) field to appear.

      Is there any simple way to achieve this?

      Thanks

      Karl

        • Minimum Values
          Kaushik Solanki

          Hi,

           

             Create a chart with dimension as Person_ID and expression as  min(CI_START_DT).

           

              Hope this will help.

           

          Regards,

          Kaushik Solanki

          • Re: Minimum Values

            Try something like this:

            LOAD

                 PERSON_ID,

                 MIN(CI_START_DT)  //You might have to use Date() around it as Sunil does in the previous answer.

            FROM

                 QVDFILE.qvd

            GROUP BY

                 PERSON_ID

            ;

              • Minimum Values
                MCPL MCPL

                Thanks vegar,

                 

                I was missing the GROUP BY function in my script.  Working great now.

                • Minimum Values

                  Hi

                   

                  I'm fairly new to Qlikview and saw this thread relates to my current difficulty in that it uses min().  I've searched through the posts and found similar questions - but I have not yet been able to achieve a good result.

                   

                  Here's a rough approximation of what's happening.  I load in several

                   

                  tables from  Excel spreadsheets - all works fine.

                   

                   

                  One table has several of the fields I am interested in eg.

                  _transactions:

                   

                  LOAD trans_ID, trans_date, trans_rating, description, value

                  FROM C:\Documents and Settings\User1\ Documents\Transaction_details.xls] ) ;

                   

                   

                   

                   

                  trans_ID     trans_date      trans_rating     location     value

                   

                  98570321  2/01/10            4                    de              123456

                  98570321   22/01/10         5                     fr               456123

                  98570321   12/05/10         2                    dk             563458

                  98570321   31/11/10         4                    au             128984

                  98570321   12/01/11        2                    us              828486

                  98570321   12/05/11         1                    nz             929476

                  98570321   22/07/11        5                    za              925556

                   

                   

                  That table - in table viewer shows correctly 12000 records - with each trans_ID having on average 12 records or so. The trans_date field shows up as a date. BTW, it is stored in excel as as date in dd/mmm/yyyy format.  I am able to use it in other parts of the Qlikview app quite successfully.

                   

                  I basically want the last date for any particular trans_ID and pop that into a text box. Sounds very simple - it should be just max(trans_date) as far as I see.  But it doesn't work. Neither does firstsortedvalue().

                  I am even able to use max(otherdate) in other parts of the app successfully. 

                   

                  In the  script I have the following:

                   

                  ...........

                  load several tables

                   

                  _data:

                   

                  Load trans_ID, firstsortedvalue(trans_ID, -trans_date) as lastTransDate resident _transactions;

                   

                  Table viewer show _data has  1060 records - which is the correct number - because it shows it has grouped successfully on trans_ID.  But using the tableviewer shows it only has trans_ID and a '-' for where I was expecting to see lastTransDate.

                   

                  Exactly the same thing happens if I take the other approach:

                   

                  _data2:

                   

                  load trans_ID, max(date#(trans_date)) as lastTransDate resident _transactions;

                   

                   

                  Yet if I leave out the max()  and just use:

                   

                  _data2:

                  load trans_ID,  (date#(trans_date)) as lastTransDate resident _transactions;

                   

                  I get trans_ID AND all the trans_dates - but of course have the 12,000 records instead of the expected 1000+

                   

                  So - for some reason - both max(date) and firstsortedvalue() are not working.   And I have   tried the set analysis approach inside the textbox properties - but still get a null for the date.

                   

                  Does anyone have a clue what I'm doing wrong?

                    • Re: Minimum Values
                      MCPL MCPL

                      Hi Brian,

                       

                      When you load the spreadsheets into Qlikview use the following script:

                       

                      LOAD

                           trans_ID

                           Max(trans_date) AS trans_date

                           trans_rating

                           location

                           value

                       

                      FROM  ........xls

                      GROUP BY trans_ID;

                       

                      This should work.

                       

                      Cheers

                        • Re: Minimum Values

                          From:

                          karllyon <qliktech@sgaur.hosted.jivesoftware.com>

                          To:

                          brian123 <brian.tangney@fitchratings.com>

                          Date:

                          11/08/2011 04:56 PM

                          Subject:

                          - Re: Minimum Values

                           

                           

                           

                           

                          QlikCommunity

                          Re: Minimum Values

                          created by karllyon in New to QlikView - View the full discussion

                          Hi Brian,

                          When you load the spreadsheets into Qlikview use the following script:

                          LOAD     trans_ID, Max(trans_date) AS trans_date, trans_rating  , location

                            ,  value

                          FROM  ........xls GROUP BY trans_ID;

                          This should work.

                           

                          Hi KArl

                           

                          Thanks for your reply. You are right - it SHOULD work - but it doesn't! I

                          have tried this already. With your example - shouldn't it   group by

                          trans_ID, trans_rating  ,  location   ,  value?

                           

                          The weird thing is - in another similar place in the app - I do

                          successfully do a max() on a date range and it works fine eg:  only({<

                          Date1={'$(=max(Date1))'}>}  [>1 - <=2 Months in Arrears %])

                           

                          Cheers

                          Reply to this message by replying to this email -or- go to the message on

                          QlikCommunity

                          Start a new discussion in New to QlikView by email or at QlikCommunity

                           

                          © 1993-2011 QlikTech International AB     Copyright & Trademarks | Privacy

                          | Terms of Use | Software EULA

                            • Re: Minimum Values
                              MCPL MCPL

                              Hi Brian,

                               

                              Try this:

                               

                              xxx:

                              LOAD   

                                    trans_ID

                                    Max(trans_date) AS trans_date

                              //    trans_rating

                              //    location

                              //    value

                              FROM  ........xls

                              GROUP BY trans_ID;

                              STORE xxx INTO xxx.qvd(qvd);

                              DROP TABLE xxx;

                               

                              Then reload your excel spreadsheet and do an INNER JOIN into the qvd file created above.

                              Should now work.

                               

                              Cheers

                              Karl

                                • Re: Minimum Values

                                  Thanks for the quick followup.

                                   

                                  So you mean modify the script as per your instructions - and then

                                  immediately after dropping table xxx

                                   

                                  write:

                                   

                                  inner join xxx.qvd , mySpreadsheet.xls  on trans_ID ;

                                   

                                  ??

                                   

                                  Could mySpreadsheet.xls be the resident table I've initially loaded in

                                  rather than having to re-load the spreadsheet again?

                                   

                                  If I've loaded it in already and it's called table1 - isit:

                                   

                                  inner join xxx.qvd , table1  on trans_ID ;

                                   

                                   

                                   

                                   

                                  I'm not clear at all on qvd files - is that a separate file in ram that

                                  gets created?

                                   

                                   

                                   

                                   

                                   

                                  From:

                                  karllyon <qliktech@sgaur.hosted.jivesoftware.com>

                                  To:

                                  brian123 <brian.tangney@fitchratings.com>

                                  Date:

                                  12/08/2011 09:29 AM

                                  Subject:

                                  - Re: Minimum Values

                                   

                                   

                                   

                                   

                                  QlikCommunity

                                  Re: Minimum Values

                                  created by karllyon in New to QlikView - View the full discussion

                                  Hi Brian,

                                   

                                  Try this:

                                   

                                  xxx:

                                  LOAD

                                        trans_ID

                                        Max(trans_date) AS trans_date

                                  //    trans_rating

                                  //    location

                                  //    value

                                  FROM  ........xls

                                  GROUP BY trans_ID;

                                  STORE xxx INTO xxx.qvd(qvd);

                                  DROP TABLE xxx;

                                   

                                  Then reload your excel spreadsheet and do an INNER JOIN into the qvd file

                                  created above.

                                  Should now work.

                                   

                                  Cheers

                                  Karl

                                  Reply to this message by replying to this email -or- go to the message on

                                  QlikCommunity

                                  Start a new discussion in New to QlikView by email or at QlikCommunity

                                   

                                  © 1993-2011 QlikTech International AB     Copyright & Trademarks | Privacy

                                  | Terms of Use | Software EULA

                                    • Re: Minimum Values
                                      MCPL MCPL

                                      Hi Brian,

                                       

                                      To get the max date you need to remove the fields trans_rating, location and value from the initial load.  These fields will not be available in the resident load, hence the need to re-load the spreadsheet.

                                       

                                      The qvd file is a qlikview data file (just like an excel spreadsheet).

                                       

                                      Please use the following and your sort should work:

                                       

                                      table1:

                                      LOAD

                                            trans_ID,

                                            Max(trans_date) AS trans_date

                                      //    trans_rating

                                      //    location

                                      //    value

                                      FROM  ........xls;

                                      GROUP BY trans_ID;

                                      STORE table1 INTO table1.qvd(qvd);

                                      DROP TABLE table1;

                                       

                                      table2:

                                      LOAD

                                            trans_ID,

                                            trans_date

                                            trans_rating

                                            location

                                            value

                                      FROM  .......xls;

                                       

                                      INNER JOIN

                                      LOAD

                                            trans_ID,

                                            trans_date

                                      FROM table1.qvd;

                                       

                                      Cheers

                                      Karl

                                        • Re: Minimum Values

                                          Thanks again for your response Karl.

                                           

                                          I did exactly as you said - and got the same result I've had with every

                                          other approach!!

                                           

                                          i.e. - a null in the trans_date field in table2:

                                           

                                          I'm stumped.

                                           

                                           

                                           

                                          From:

                                          karllyon <qliktech@sgaur.hosted.jivesoftware.com>

                                          To:

                                          brian123 <brian.tangney@fitchratings.com>

                                          Date:

                                          12/08/2011 10:06 AM

                                          Subject:

                                          - Re: Minimum Values

                                           

                                           

                                           

                                           

                                          QlikCommunity

                                          Re: Minimum Values

                                          created by karllyon in New to QlikView - View the full discussion

                                          Hi Brian,

                                           

                                          To get the max date you need to remove the fields trans_rating, location

                                          and value from the initial load.  These fields will not be available in

                                          the resident load, hence the need to re-load the spreadsheet.

                                           

                                          The qvd file is a qlikview data file (just like an excel spreadsheet).

                                           

                                          Please use the following and your sort should work:

                                           

                                          table1:

                                          LOAD

                                                trans_ID,

                                                Max(trans_date) AS trans_date

                                          //    trans_rating

                                          //    location

                                          //    value

                                          FROM  ........xls;

                                          GROUP BY trans_ID;

                                          STORE table1 INTO table1.qvd(qvd);

                                          DROP TABLE table1;

                                           

                                          table2:

                                          LOAD

                                                trans_ID,

                                                trans_date

                                                trans_rating

                                                location

                                                value

                                          FROM  .......xls;

                                           

                                          INNER JOIN

                                          LOAD

                                                trans_ID,

                                                trans_date

                                          FROM table1.qvd;

                                           

                                          Cheers

                                          Karl

                                          Reply to this message by replying to this email -or- go to the message on

                                          QlikCommunity

                                          Start a new discussion in New to QlikView by email or at QlikCommunity

                                           

                                          © 1993-2011 QlikTech International AB     Copyright & Trademarks | Privacy

                                          | Terms of Use | Software EULA

                                            • Re: Minimum Values
                                              MCPL MCPL

                                              Hi Brian,

                                               

                                              I have taken the sample data you provided above and created an xls file (brian_test.xls).  Note there is an error in one of the dates (31-November).

                                               

                                              I then applied the following script:

                                               

                                              table1:

                                              LOAD trans_ID,

                                              MAX(trans_date) AS trans_date

                                              //     trans_rating,

                                              //     location,

                                              //     value

                                              FROM

                                              J:\Qlikview\Sandbox\brian_test.xls

                                              (biff, embedded labels, table is Sheet1$)

                                              GROUP BY trans_ID;

                                              STORE table1 INTO table1.qvd(qvd);

                                              DROP TABLE table1;

                                               

                                              table2:

                                              LOAD trans_ID,

                                              trans_date,

                                              trans_rating,

                                              location,

                                              value

                                              FROM

                                              J:\Qlikview\Sandbox\brian_test.xls

                                              (biff, embedded labels, table is Sheet1$);

                                               

                                              INNER JOIN

                                              LOAD trans_ID,

                                              trans_date

                                              FROM

                                              J:\Qlikview\Sandbox\table1.qvd

                                              (qvd);

                                               

                                               

                                              Output is as follows:

                                               

                                              brian.bmp

                                               

                                              Maybe there is a data integrity issue with your spreadsheet.

                                               

                                              Cheers

                                              Karl

                                                • Re: Minimum Values

                                                  Thanks

                                                   

                                                  I can see how that would be right.

                                                   

                                                  But I am beginning to suspect the cause of the problem lies in the way

                                                  Qlikview is assembling the tables internally.  This may be why a max(date)

                                                  works in one location yet not in others .

                                                   

                                                  I have 5 main spreadsheets with several common fields and derive another 5

                                                  or so tables from these.

                                                   

                                                   

                                                  I'm looking at tableviewer and seeing several syn tables  and have pared

                                                  away at things to get them down from 8 to 4.

                                                  I think I recall from reading the manual that you want as few syn tables

                                                  as possible.  Is that correct - is the ideal zero syn tables?  I come from

                                                  a SQL server background - so it's easy to confuse me at this stage!

                                                   

                                                   

                                                   

                                                   

                                                   

                                                  From:

                                                  karllyon <qliktech@sgaur.hosted.jivesoftware.com>

                                                  To:

                                                  brian123 <brian.tangney@fitchratings.com>

                                                  Date:

                                                  12/08/2011 10:56 AM

                                                  Subject:

                                                  - Re: Minimum Values

                                                   

                                                   

                                                   

                                                   

                                                  QlikCommunity

                                                  Re: Minimum Values

                                                  created by karllyon in New to QlikView - View the full discussion

                                                  Hi Brian,

                                                   

                                                  I have taken the sample data you provided above and created an xls file

                                                  (brian_test.xls).  Note there is an error in one of the dates

                                                  (31-November).

                                                   

                                                  I then applied the following script:

                                                   

                                                  table1:

                                                  LOAD trans_ID,

                                                  MAX(trans_date) AS trans_date

                                                  //     trans_rating,

                                                  //     location,

                                                  //     value

                                                  FROM

                                                  J:\Qlikview\Sandbox\brian_test.xls

                                                  (biff, embedded labels, table is Sheet1$)

                                                  GROUP BY trans_ID;

                                                  STORE table1 INTO table1.qvd(qvd);

                                                  DROP TABLE table1;

                                                   

                                                  table2:

                                                  LOAD trans_ID,

                                                  trans_date,

                                                  trans_rating,

                                                  location,

                                                  value

                                                  FROM

                                                  J:\Qlikview\Sandbox\brian_test.xls

                                                  (biff, embedded labels, table is Sheet1$);

                                                   

                                                  INNER JOIN

                                                  LOAD trans_ID,

                                                  trans_date

                                                  FROM

                                                  J:\Qlikview\Sandbox\table1.qvd

                                                  (qvd);

                                                   

                                                   

                                                  Output is as follows:

                                                   

                                                  http://community.qlik.com/servlet/JiveServlet/downloadImage/2-140490-6457/296-64/brian.bmp

                                                   

                                                   

                                                  Maybe there is a data integrity issue with your spreadsheet.

                                                   

                                                  Cheers

                                                  Karl

                                                  Reply to this message by replying to this email -or- go to the message on

                                                  QlikCommunity

                                                  Start a new discussion in New to QlikView by email or at QlikCommunity

                                                   

                                                  © 1993-2011 QlikTech International AB     Copyright & Trademarks | Privacy

                                                  | Terms of Use | Software EULA

                                                    • Re: Minimum Values
                                                      MCPL MCPL

                                                      Hi Brian,

                                                       

                                                      no syn tables is the primary objective (you then have full control over the data - not Qlikview).  You will probably find that your problem disappears.

                                                        • Re: Minimum Values

                                                          Right - so I will aim for zero syn tables.   I just noticed another thing

                                                          now - in the tables where max(date) was successful - the date is stored as

                                                          a number.

                                                           

                                                           

                                                           

                                                           

                                                           

                                                          From:

                                                          karllyon <qliktech@sgaur.hosted.jivesoftware.com>

                                                          To:

                                                          brian123 <brian.tangney@fitchratings.com>

                                                          Date:

                                                          12/08/2011 11:57 AM

                                                          Subject:

                                                          - Re: Minimum Values

                                                           

                                                           

                                                           

                                                           

                                                          QlikCommunity

                                                          Re: Minimum Values

                                                          created by karllyon in New to QlikView - View the full discussion

                                                          Hi Brian,

                                                           

                                                          no syn tables is the primary objective (you then have full control over

                                                          the data - not Qlikview).  You will probably find that your problem

                                                          disappears.

                                                          Reply to this message by replying to this email -or- go to the message on

                                                          QlikCommunity

                                                          Start a new discussion in New to QlikView by email or at QlikCommunity

                                                           

                                                          © 1993-2011 QlikTech International AB     Copyright & Trademarks | Privacy

                                                          | Terms of Use | Software EULA

                                                          • Re: Minimum Values

                                                            Problem solved!

                                                             

                                                            After seeing that other date fields converted OK - I went back to the

                                                            excel worksheet.  I saw all the dates were in dd/mmm/yy format - so forced

                                                            them all over to dd/mm/yy.  Moral of story - don't trust an excel column

                                                            to be what it appears to be.

                                                             

                                                            I re-ran the script - and finally got an entry in the text box.  (a

                                                            numeric - which I re-formatted back to date)

                                                             

                                                            So, traps for young players -- something so simple! And yet it has taken

                                                            about 6 hours to resolve this issue. The silver lining is I got to learn a

                                                            bit more about associative databases and set analysis!

                                                             

                                                             

                                                            Thanks for your help.

                                                             

                                                             

                                                             

                                                             

                                                             

                                                            From:

                                                            karllyon <qliktech@sgaur.hosted.jivesoftware.com>

                                                            To:

                                                            brian123 <brian.tangney@fitchratings.com>

                                                            Date:

                                                            12/08/2011 11:57 AM

                                                            Subject:

                                                            - Re: Minimum Values

                                                             

                                                             

                                                             

                                                             

                                                            QlikCommunity

                                                            Re: Minimum Values

                                                            created by karllyon in New to QlikView - View the full discussion

                                                            Hi Brian,

                                                             

                                                            no syn tables is the primary objective (you then have full control over

                                                            the data - not Qlikview).  You will probably find that your problem

                                                            disappears.

                                                            Reply to this message by replying to this email -or- go to the message on

                                                            QlikCommunity

                                                            Start a new discussion in New to QlikView by email or at QlikCommunity

                                                             

                                                            © 1993-2011 QlikTech International AB     Copyright & Trademarks | Privacy

                                                            | Terms of Use | Software EULA

                                        • Re: Minimum Values

                                          Hi

                                           

                                          I'm fairly new to Qlikview and saw this thread relates to my current difficulty in that it uses min().  I've searched through the posts and found similar questions - but I have not yet been able to achieve a good result.

                                           

                                          Here's a rough approximation of what's happening.  I load in several

                                           

                                          tables from  Excel spreadsheets - all works fine.

                                           

                                           

                                          One table has several of the fields I am interested in eg.

                                          _transactions:

                                           

                                          LOAD trans_ID, trans_date, trans_rating, description, value

                                          FROM C:\Documents and Settings\User1\ Documents\Transaction_details.xls] ) ;

                                           

                                           

                                           

                                           

                                          trans_ID     trans_date      trans_rating     location     value

                                           

                                          98570321  2/01/10            4                    de              123456

                                          98570321   22/01/10         5                     fr               456123

                                          98570321   12/05/10         2                    dk             563458

                                          98570321   31/11/10         4                    au             128984

                                          98570321   12/01/11        2                    us              828486

                                          98570321   12/05/11         1                    nz             929476

                                          98570321   22/07/11        5                    za              925556

                                           

                                           

                                          That table - in table viewer shows correctly 12000 records - with each trans_ID having on average 12 records or so. The trans_date field shows up as a date. BTW, it is stored in excel as as date in dd/mmm/yyyy format.  I am able to use it in other parts of the Qlikview app quite successfully.

                                           

                                          I basically want the last date for any particular trans_ID and pop that into a text box. Sounds very simple - it should be just max(trans_date) as far as I see.  But it doesn't work. Neither does firstsortedvalue().

                                          I am even able to use max(otherdate) in other parts of the app successfully. 

                                           

                                          In the  script I have the following:

                                           

                                          ...........

                                          load several tables

                                           

                                          _data:

                                           

                                          Load trans_ID, firstsortedvalue(trans_ID, -trans_date) as lastTransDate resident _transactions;

                                           

                                          Table viewer show _data has  1060 records - which is the correct number - because it shows it has grouped successfully on trans_ID.  But using the tableviewer shows it only has trans_ID and a '-' for where I was expecting to see lastTransDate.

                                           

                                          Exactly the same thing happens if I take the other approach:

                                           

                                          _data2:

                                           

                                          load trans_ID, max(date#(trans_date)) as lastTransDate resident _transactions;

                                           

                                           

                                          Yet if I leave out the max()  and just use:

                                           

                                          _data2:

                                          load trans_ID,  (date#(trans_date)) as lastTransDate resident _transactions;

                                           

                                          I get trans_ID AND all the trans_dates - but of course have the 12,000 records instead of the expected 1000+

                                           

                                          So - for some reason - both max(date) and firstsortedvalue() are not working.   And I have   tried the set analysis approach inside the textbox properties - but still get a null for the date.

                                           

                                          Does anyone have a clue what I'm doing wrong?