Solved: Re: Minimum Values - Qlik Community

Anonymous · ‎2011-08-05

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

Report Inappropriate Content · ‎2011-08-05

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

;

View solution in original post

kaushiknsolanki · ‎2011-08-05

Hi,

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

Hope this will help.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

SunilChauhan · ‎2011-08-05

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

Sunil Chauhan

Report Inappropriate Content · ‎2011-08-05

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

;

Anonymous · ‎2011-08-07

Thanks vegar,

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

Report Inappropriate Content · ‎2011-08-11

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?

Report Inappropriate Content · ‎2011-08-11

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?

Anonymous · ‎2011-08-11

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

Report Inappropriate Content · ‎2011-08-11

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

Anonymous · ‎2011-08-11

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