Simple Question

[#1]

Hi Guys:

I have a very simple question but not able to get the answer hope that you guys can help.

In Qlikviw when we select the Year, Month and Day for sale we will be getting the transaction for that particular day. For example: - Year 2011 Month 8 and Day 8 we will get the transaction $200.

If we want the sale from 1-8 of August which is the beginning of the month to the selected day. How can I do that?

Sorry for such a simple question and hope that the solution will be simple too by the way I can not use the set analysis function.

Thanks Guys

[#2]

Yes, you are in right direction now.

Cheers.

[#3]

Hi Abhinava:

Sorry that I did not make myself clear.

What I mean is that for the Expression below there are 2 years (Year and year) and also 2 month(Month1 and month) fields.

Sum(if((Year = year(CurrentDate)) and (Month1 = month(CurrentDate)) and ([Inv. Date] <= CurrentDate) and (invtype='40' or invtype='20'),goodqty))

From my script I am able to get one year and month field. (See below edit script)

The Year will be from 2006 to 2011.

In Script:-

Year(transdate) as Year

The month is in Jan, Feb,March…

So I need to change to number field 1,2,3…And that field is Month1 (see below Edit Script)

In Script:-

num(Month (transdate)) as Month1,

I can not use month because it is not in number field right?

But for the Year it is in number already so I don’t need to change the field to number right?

For the Expression:-

Sum(if((Year = year(CurrentDate)) and (Month1 = month(CurrentDate)) and ([Inv. Date] <= CurrentDate) and (invtype='40' or invtype='20'),goodqty))

I will use the Year  and year the same thing right?

Whereas for the month field I will use the Month1 and not the month because the month field is not in number field right?

That will leave me with the same Year field for both Year  and for month field I will only have Month1.

Hope that I can make myself clear here if not I will try to explain again.

LOAD

    transdate,

    Day(transdate) as Transday,

    Year(transdate) as Year,//Because of syn$

    month (transdate) as monthdes,  //will be in Jan,Feb, Format

    num(Month(transdate)) as month,

    num(Month (transdate)) as Month1,

    invtype,

goodqty,

Thanks a lot for your help

[#4]

Hi

I'm fairly new to Qlikview and saw this thread relates to my current difficulty.  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

table 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?

[#5]

Hi,

You must use group by in your second data load:

_data2:

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

[#6]

From:

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

To:

brian123 <brian.tangney@fitchratings.com>

Date:

11/08/2011 04:33 PM

Subject:

- Re: Simple Question

QlikCommunity

Re: Simple Question

created by BlackRockS in Development (QlikView Desktop) - View the full

discussion

Hi,

You must use group by in your second data load:

_data2:

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

transactions group by transID;

Thanks Blackrock, I must have neglected to include that 'group by' clause

when I was retyping it into the email

I have it in the script exactly as you have suggested but still get blanks

in the date field in table data2. The 1000 or so TransID's are all

there though. So it is working and is grouping - just not able to

reproduce the date field in the derived table.

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

| Terms of Use | Software EULA

[#7]

Hi,

Seems like you have an issue with the date fromat and the way you are using the date#() funtion, you may need to understand them first.

Please look at the sample attached where i can see max date is loading perfectly for the above sample data.

Cheers.