topic Re: qvd issue ? urgent in QlikView

qvd issue ? urgent

Mon, 20 Oct 2014 05:58:44 GMT

Hi All,

I have a qvd data with no.of customer codes..

for testing purpose I have taken randomly 3 customer codes in Excel , I have worked on that..

So Balance amount & Overdue amounts are coming fine with that 3 customer codes ...

When I am using QVD by replacing that Excel, I am getting Overdue amount Entire column is Zero...

Help me on this ..

Regards,

Helen

Re: qvd issue ? urgent

anbu1984 — Mon, 20 Oct 2014 06:13:25 GMT

Can you post the script

Re: qvd issue ? urgent

maxgro — Mon, 20 Oct 2014 06:15:22 GMT

and qvd and xls?

Re: qvd issue ? urgent

Mon, 20 Oct 2014 06:16:57 GMT

FT_OS:

LOAD

OST_MAIN_ACNT_CODE as MS_MAIN_ACNT_CODE,

OST_SUB_ACNT_CODE as CUST_CODE1,

OST_SUB_ACNT_CODE,

OST_DOC_NO,

OST_DRCR_FLAG,

if(isnull(OST_FC_ADJ_AMT),0,OST_FC_ADJ_AMT) as OST_FC_ADJ_AMT,

if(isnull(OST_FC_ORG_AMT),0,OST_FC_ORG_AMT) as OST_FC_ORG_AMT,

// Sum(OST_FC_ORG_AMT-OST_FC_ADJ_AMT) as DebtorsOutStandingAmount,

// if(OST_DUE_DT<Today(),sum(OST_FC_ORG_AMT-OST_FC_ADJ_AMT)) as DebtorsOverdueAmount,

OST_DOC_DT,

date#(date(OST_DUE_DT,'MM/DD/YYYY')) as OST_DUE_DT,

// date(OST_DUE_DT)as OST_DUE_DT ,

if(isnull(OST_FC_ADJ_AMT),0,OST_FC_ADJ_AMT)-OST_FC_ORG_AMT as diff_val

//from

//D:\DSO.csv

//(txt, utf8, embedded labels, delimiter is ',', msq);

Resident Gen where wildmatch(OST_SUB_ACNT_CODE,'100793','101242','102115');

Because of Date format for OST_DUE_DT & Today

Regards,

Helen

Re: qvd issue ? urgent

Anonymous — Mon, 20 Oct 2014 06:17:51 GMT

Helen,

Maybe QV is not recognizing your number format. So try like this adapting your input and output number format:

load customer

num(num#(amountfield, '0.#0'), '0,##') as amountfield

from yoursource;

Marc.

Re: qvd issue ? urgent

Mon, 20 Oct 2014 06:26:09 GMT

Initially your QVD did not had the customer codes, did you recreated the QVD with new codes?

Re: qvd issue ? urgent

Mon, 20 Oct 2014 06:29:15 GMT

In my QVD i am having Customer codes, For testing purpose I have take few customers...

=(Sum({<OST_DUE_DT={"<$(=today())"},OST_DRCR_FLAG={'C'}>}OST_FC_ADJ_AMT)-sum({<OST_DUE_DT={"<$(=today())"},OST_DRCR_FLAG={'C'}>}OST_FC_ORG_AMT))+

(Sum({<OST_DUE_DT={"<$(=today())"},OST_DRCR_FLAG={'D'}>}OST_FC_ORG_AMT)-sum({<OST_DUE_DT={"<$(=today())"},OST_DRCR_FLAG={'D'}>}OST_FC_ADJ_AMT))

Because of OST_DUE_DT date format this problem is coming...

Re: qvd issue ? urgent

robert99 — Mon, 20 Oct 2014 06:30:17 GMT

Hi Betty

Have you read this blog post

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

point 1 especially

Although Im unsure why it would be the right date format loaded from Excel but not a qvd.

Re: qvd issue ? urgent

Mon, 20 Oct 2014 06:50:20 GMT

My OST_DUE_DT is in format: 1/4/2010 12:00:00 AM

Today Date: 10/20/2014

I need compare this OST_DUE_DT with Today in Set Anaysis, SO that how can i convert my OST_DUE_DT in script....

Re: qvd issue ? urgent

jonathandienst — Mon, 20 Oct 2014 06:54:04 GMT

I think this:

{<OST_DUE_DT={"<$(=Date(today(), 'D/M/YYYY hh:mm:ss t'))"}

HTH

Jonathan

Re: qvd issue ? urgent

Mon, 20 Oct 2014 06:59:07 GMT

You can create a variable and then use it as well.

vToday

=Date(today(), 'D/M/YYYY 12:00:00 AM')

And then use vToday in set analysis

{"<$(vToday)"} or {"<$(=vToday)"}

Re: qvd issue ? urgent

PrashantSangle — Mon, 20 Oct 2014 07:00:46 GMT

Hi,

Use below code to format your date

Date(Floor(OST_DUE_DT),'DD/MM/YYYY')

Regards

Re: qvd issue ? urgent

Anonymous — Mon, 20 Oct 2014 07:02:14 GMT

Helen,

If you want to do it in script, then try:

load ...

Date(Date#(OST_DUE_DT, 'D/M/YYYY hh:mm:ss t'), 'DD/MM/YYYY') as OST_DUE_DT

from yoursource;

Marc.

Re: qvd issue ? urgent

Anonymous — Mon, 20 Oct 2014 07:27:22 GMT

Helen,

To make sure you are getting the correct format date, try always load your date fields with the Date function. Replace in script ALL your date fields by

Date(datefield, 'DD/MM/YYYY') as datefield

If your field has the right format (excel input) it will ensure the format, and if your source is your qvd, it will force to the date format.

Hope it helps.

Marc.