Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
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
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
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:
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
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
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:
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
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.
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:
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
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:
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