topic Re: Minimum Values in QlikView

Minimum Values

Anonymous — Fri, 05 Aug 2011 06:18:53 GMT

Hi,

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

Sample data below:

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

kaushiknsolanki — Fri, 05 Aug 2011 07:05:20 GMT

Hi,

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

Hope this will help.

Regards,

Kaushik Solanki

Minimum Values

SunilChauhan — Fri, 05 Aug 2011 07:10:07 GMT

Date(min(CI_START_DT)) in eexpression will give in date format

Re: Minimum Values

Fri, 05 Aug 2011 07:42:23 GMT

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

Anonymous — Sun, 07 Aug 2011 23:26:35 GMT

Thanks vegar,

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

Minimum Values

Thu, 11 Aug 2011 06:28:42 GMT

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

Thu, 11 Aug 2011 06:29:06 GMT

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

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;

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

Anonymous — Thu, 11 Aug 2011 06:56:29 GMT

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

Thu, 11 Aug 2011 23:24:38 GMT

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

| Terms of Use | Software EULA

Re: Minimum Values

Anonymous — Thu, 11 Aug 2011 23:29:21 GMT

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

Thu, 11 Aug 2011 23:47:28 GMT

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

| Terms of Use | Software EULA

Re: Minimum Values

Anonymous — Fri, 12 Aug 2011 00:06:33 GMT

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

Fri, 12 Aug 2011 00:33:44 GMT

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

| Terms of Use | Software EULA

Re: Minimum Values

Anonymous — Fri, 12 Aug 2011 00:55:52 GMT

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:

Maybe there is a data integrity issue with your spreadsheet.

Cheers

Karl

Re: Minimum Values

Fri, 12 Aug 2011 01:54:14 GMT

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

| Terms of Use | Software EULA

Re: Minimum Values

Anonymous — Fri, 12 Aug 2011 01:57:25 GMT

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

Fri, 12 Aug 2011 03:21:21 GMT

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

| Terms of Use | Software EULA

Re: Minimum Values

Fri, 12 Aug 2011 04:31:02 GMT

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

| Terms of Use | Software EULA