Minimum Values

[Anonymous]

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

[#2]

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

[Anonymous]

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

[#4]

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

[Anonymous]

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

[#6]

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

[Anonymous]

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.

[#8]

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

[#9]

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