topic Help on Set Analysis in QlikView

Help on Set Analysis

Sat, 24 Dec 2011 10:47:56 GMT

Dear All,

Any one please help on set analysis, I can found that what I am doing wrong.

please help, attaching a sample here

thanks

anil

Help on Set Analysis

Tue, 27 Dec 2011 07:48:11 GMT

Check date format on vEndDt and docDate so you don't compare apples with pears.

Help on Set Analysis

forte — Tue, 27 Dec 2011 08:08:52 GMT

Hi Anilsknp ,

As Thomas said, try cheking date format for docdate field on your load script ( just using date(docdate) as docdate each time yo load docdate on your script) and it will work correctly.

Hope it helps ,

Regards

Help on Set Analysis

Tue, 27 Dec 2011 08:35:59 GMT

thanks to both of you for reply,

but can u explain more what you are saying and can u correct me in my attached QV example what I am doing wrong.

Thanks & Regards

anil

Help on Set Analysis

forte — Tue, 27 Dec 2011 08:45:05 GMT

Hi Anilsknp ,

I just copy your load script and just paint , in bold and underlined , what you need to change in order to solve your problem ...

Party:
LOAD iPartyId,
sPartyName,
SalesRep,
sPartyGroupName,
unit1,
unit2,
unit3,
sml,
prtZone;
SQL SELECT *
FROM ERPDATA.dbo.viewQVBasicSalesParty;

SML:
LOAD iPartyId,
SML;
SQL SELECT *
FROM ERPDATA.dbo.viewQVBasicSalesPartyTonsSML;

Machine:
LOAD macId,
sMachineNo,
sMacTypeName,
sPaperTypeName,
iTPD,
iTPM,
iTPY,
posId,
sMacPositionName,
sMacSubPositionName,
iOMacSpeedTo,
iInvLength,
iInvWidth;
SQL SELECT *
FROM ERPDATA.dbo.viewQVBasicSalesMachineDet;

ProductGroup:
LOAD iItemGroupId,
sItemGroupName,
grpName;
SQL SELECT *
FROM ERPDATA.dbo.viewQVBasicSalesProductGroups;

FinYear:
LOAD

Date(docDate) as docDate,
DayNo,
month(docDate) as month,
MonthName(docDate) as mntName,
mntNo,
Quarter,
curQuarter,
fyYear1,
fyYear2,
fyDate1,
fyDate2,
curFyDate1,
curFyDate2,
nxtFyDate1,
nxtFyDate2,
lst1Date,
lst2Date,
lst3Date,
lst4Date,
lst5Date,
lst1FyDate1,
lst1FyDate2,
lst2FyDate1,
lst2FyDate2,
lst3FyDate1,
lst3FyDate2,
lst4FyDate1,
lst4FyDate2,
lst5FyDate1,
lst5FyDate2,
lst5FinYear,
lst4FinYear,
lst3FinYear,
lst2FinYear,
lst1FinYear,
"Financial Year",
curFinYear,
nxtFinYear;
SQL SELECT *
FROM ERPDATA.dbo.viewQvFinanceCurNxtLstFinYear;

MaxSalDate:
LOAD "Financial Year",
fyMaxSalTrnDate;
SQL SELECT *
FROM ERPDATA.dbo.viewQvFinMaxSalTrnDate;

Finance:
LOAD trnType,
docId,
docNo,
Date(docDate) as docDate,

sourceNo,
sourceDate,
iSrNo,
iPartyId,
iPymtCommitDays,
iMaxPymtCommitDays,
iItemGroupId,
sItemName,
iWireNo,
posId,
iLength,
iWidth,
Sqmtr,
iAgrPrice,
iAgrRate,
iInvoiceAmountINR,
iExport,
sPymtModeName,
sDocMediaName,
invGrTotal,
collAmt,
crnAmt,
outStndAmt,
outStndAgeToDays,
recdOn,
recdInDays;
SQL SELECT *
FROM ERPDATA.dbo.viewQvFinanceZZZFinal;

InvDet:
LOAD sInvNo,
Date(docDate) as docDate,

invAmt,
dVchDate,
iAmount;
SQL SELECT *
FROM ERPDATA.dbo.viewQvFinanceInvDetail;

Regards

Help on Set Analysis

sridhar240784 — Tue, 27 Dec 2011 08:47:39 GMT

Your date formate in Docdate and Variable are in different formates.

Variable $(vEndDt) Returns - 4.8098505739755e-005

Docdate has MM/DD/YYYY hh:mm:ss TT formate.

if i properly understood your requirement, You need to create you Docdate with out timestampe and equate same with variable $(vEndDt)

Hope i`m clear.

-Sridhar

Help on Set Analysis

Tue, 27 Dec 2011 08:53:18 GMT

hi, forte

thanks for quick reply, I am trying in script what you suggest

anil

Help on Set Analysis

Tue, 27 Dec 2011 08:56:03 GMT

hi, Sri

Thanks for your reply

I am trying to get EndDate of every month and then I am trying to use it in Set analysis. to get the outstanding value for the each month.

anil

Re: Help on Set Analysis

Sokkorn — Tue, 27 Dec 2011 09:14:37 GMT

Hi Anil,

Two thing to do:

1. Change variable vEndDt = DATE(monthend(max(docDate)) ,'M/D/YYYY hh:mm:ss TT')

2. Change expression =max(vEndDt) to =vEndDt

See the sample attached file also.

Let me know if this one help you.

Regards,

Sokkorn Cheav