Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !!
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.
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
Hi,
The syntax is:
Load field1,
field2,
aggr(field3)
resident tablename
group by field1,field2;
TestTable:
Load
field1,
field2,
|
|
|
min(DateField) as DateField
from tableName group by field1,field2,field3-------------------------;
thank you,
i will try it later, and post the result here
thank you so much for your help
cheerss
regardss
it's work, thank you for the help santosh
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
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!!
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