ASK.. how to LOAD MIN(DATE) in qlikview??

[#1]

hello there, need help plis 😄

really need your help, this is urgent, plis help.. thank you before, i really apreciate it.

plis read and see the picture clearly, and you will understand what i mean..

i want to make a financial repot about agingday, duedate, etc

my case is something related with buying credits.

how can i load min(date) in qlikview??

this is my condtion,

i have a field named "CorAccountDetail" , which ID (NoRek+Nopin) is 4007014852-001, and have 13 record, because customer buying something and credit in 13 times.

see the picture below (IN SQL)

this is my QV load screen,,

note , I load it from my .qvd file.

PICTURE ABOVE CAN RUN, and IT'S WORK, Succes load !!

BUT

if i type,

num(interval(today() - date(min(CicilDate))) as AgingDay or

num(interval(today() - min(date(CicilDate))) as AgingDay or

min(CicilDate) as MinCicildate or etc all doen't work, it occured error

this is my qv table ***TABLE 1

in two chart above, (just said chart A(AgingDay) and chart B(1-30))

339.000 divided into all the period from 1-30, 31-60.... , 90 - 150. because norek+nopin 4007014852-001 have more than 1 CicilDate,

so calculate in expression will result in more than 1 agingday. which suit each period in table B.

what i want is all the debt will shown up in field 90-150 which total debt is 1.596.000 because norek+nopin 4007014852-001 maximum aging day is 105.

to do that i must get min(cicildate), so 1 norek+nopin only has 1 cicildate.

and this is the expression in each period(just change the period in AgingPeriod)

and in table i can get min(date) using this expression, but in load script it doesn't work, because of that, my financial report have an eror.

the problem is,

see TABLE 1, the agingday there is 105, so sum(angsuran)which is 1.596.000 must be in column 91-150, but in that chart it divided into all period.i know the reason, because my expression calculated all the cicilDate in that NoRek.CorAccountDetail. (there is 4 cicildate where paidstatus = 0, so it calculated all the cicildate and sum(angsuran) divided into each period).

so i just only need the min(cicildate) and sum(angsuran) placed in "period of min(cicildate)"

when i type min(CicilDate) in load script, it occured eror.

my question is,

how to load min(CicilDate) into qlikview??

so i can load my AgingDay correctly,

Num(Interval(today() - min(date(CicilDate))) as AgingDay. <----- this doesn't work

now, in CorAccountDetail, one (NoRek+NoPin) have more than one cicil date (see the TABLE 1).

sooo i just want to get, one (NoRek+NoPin) only have one cicildate (WHICH IS MIN(CICILDATE))

thank you for the help,

sorry my english is poor =P

Regards !!

[#2]

It seems like u are using min without taking group by.

As per my understanding create a separate table having min(DateField) group by the dimension u need to take for having min value & link with your table on DateField.

Try this & let me know your findings.

[#3]

yes, i think that's the problem.

but i do not know what is the syntax of group by,

can you plis give me an example of group by syntax? or just give me the group by syntax based on my case

thank you very much

[#4]

Hi,

The syntax is:

Load field1,
 field2,
 aggr(field3)
resident tablename
group by field1,field2;

[#5 · Accepted solution]

TestTable:

Load

field1,

field2,

|

|

|

min(DateField) as DateField

from tableName group by field1,field2,field3-------------------------;

[#6]

thank you,

i will try it later, and post the result here

thank you so much for your help

cheerss

regardss

[#7]

it's work, thank you for the help santosh

[#8]

ASK Again, need help

Hello All,

thank's to your help, now i can load min(cicildate) with this code,

load

NoRek as NoRek_CAD,

NoPin as NoPin_CAD,

min(date(CicilDate)) as MinCicilDate,

num(interval(today() - min(Date(CicilDate)))) as AgingDay,

from

(qvd) group by NoRek, NoPin;

but now i have another problem, which you may have already know,

the problem is how to select,

min(date(CicilDate)) where PaidSts = 0 As MinCicilDay,

because the real agingday is (today - MinCicilDay where PaidSts = 0)

What is the syntax of WHERE?

plis complete my syntax.

thank you for your help..

regards!!

[#9]

U can try this syntax

load

NoRek as NoRek_CAD,

NoPin as NoPin_CAD,

min(date(CicilDate)) as MinCicilDate,

num(interval(today() - min(Date(CicilDate)))) as AgingDay,

from

(qvd) group by NoRek, NoPin;