topic Re: repetative values. in QlikView

repetative values.

Anonymous — Mon, 20 Oct 2014 21:32:19 GMT

Hey data i've imported contains several duplications, I've tried distinct load but to no luck. could you please suggest some course of actions to remove the duplications?

at present it looks something like...

balanceCurrent | balanceFuture | PostingDate | documentDate

433.50 -230.53 30/09/2014 30/09/2014

202.97 -108.24 1/09/2014 28/06/2014

202.97 -108.24 30/09/2014 30/09/2014

94.73 0.00 1/09/2014 28/06/2014

94.73 0.00 30/09/2014 30/09/2014

thanks
Bon.

Re: repetative values.

maxgro — Mon, 20 Oct 2014 21:52:29 GMT

see attachment

if this is your req (distinct balance)

Re: repetative values.

Anonymous — Mon, 20 Oct 2014 22:01:34 GMT

Hey Massimo,

thank you for your reply.

Yes that is something im looking for. I have tried using disctinct in the script but it still showing the same thing. should I declare it as an expression?

Re: repetative values.

MarcoWedel — Mon, 20 Oct 2014 22:14:02 GMT

Hi,

distinct load seems to work for your sample data:

LOAD Distinct * INLINE [

balanceCurrent, balanceFuture, PostingDate, documentDate

433.50, -230.53, 30/09/2014, 30/09/2014

202.97, -108.24, 1/09/2014, 28/06/2014

202.97, -108.24, 30/09/2014, 30/09/2014

94.73, 0.00, 1/09/2014, 28/06/2014

94.73, 0.00, 30/09/2014, 30/09/2014

];

can you post your app?

regards

Marco

Re: repetative values.

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

Hey Massimo

could you please upload a .txt instead? im using the personal edition and can no longer open other qvw's.

thanks.

Bon.

Re: repetative values.

Anonymous — Mon, 20 Oct 2014 22:16:34 GMT

Hey Marco.

thank you for your reply, my datasource is coming from a odbc, not sure if will make any difference as to what you've suggested.

Re: repetative values.

MarcoWedel — Mon, 20 Oct 2014 22:18:48 GMT

there must be some difference in the values if distinct does not work.

Re: repetative values.

Anonymous — Mon, 20 Oct 2014 22:33:24 GMT

I've been trying to look for the differences but cant seem to find it. is there a way to specify that if balanceCurrent, balanceFuture, postingDate and DocumentDate matches remove the row? would that be my best approach? or would the problem be with my calendar?

APPeriodSummary:

LOAD Distinct
balanceCurrent,
     balanceFuture,
     current,
     futureBalance,
     invoices,
     oid as myAPPeriodSummary,
     payments,
     purchases,
     totalBalance
FROM
[..\APPeriodSummary.qvd](qvd);

APInvoice:

LOAD distinct
postingDate,
lcNetAmount,
     oid as myAPInvoice,
     paymentDate,
     documentDate,
     reference
FROM
[..\APInvoice.qvd](qvd);

Temp:

Load
              min(postingDate) as minDate,
               max(postingDate) as maxDate
Resident APInvoice;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
              $(varMinDate) + Iterno()-1 As Num,
               Date($(varMinDate) + IterNo() - 1) as TempDate
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
Load distinct
   Dual(fYear-1 &'/'& fYear, fYear) as FYear,   // Dual fiscal year
   Dual(Month, fMonth)                as FMonth, // Dual fiscal month
   Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,
   Ceil((postingDate-StartOfFWeekOne+1)/7) as FWeekNo,
   *;
Load Year + If(Month>=$(vFM), 1, 0)   as fYear,   // Numeric fiscal year
   Mod(Month-$(vFM), 12)+1            as fMonth, // Numeric fiscal month
   Dual('Q' & Ceil(Month/3), Ceil(Month/3)) as Quarter,
   WeekStart(FYearStart,0,$(vFD))     as StartOfFWeekOne,
   *;
MasterCalendar:
Load
               TempDate AS postingDate,
               week(TempDate) As Week,
               Year(TempDate) As Year,
               Month(TempDate) As Month,
               Day(TempDate) As Day,
               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
               YearStart(TempDate,0,$(vFM)) as FYearStart,
              Week(TempDate) & '-' & Year(TempDate) as WeekYear,
   Dual(WeekDay(TempDate),Mod(WeekDay(TempDate-$(vFD)),7)+1) as WeekDay,
    Date(TempDate,'MM/DD') as DATEMMDD
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

thanks.

Bon.

Re: repetative values.

maxgro — Mon, 20 Oct 2014 23:00:12 GMT

load * inline [

balanceCurrent , balanceFuture , PostingDate , documentDate

433.50 , -230.53 , 30/09/2014 , 30/09/2014

202.97 , -108.24 , 1/09/2014 , 28/06/2014

202.97 , -108.24 , 30/09/2014 , 30/09/2014

94.73 , 0.00 , 1/09/2014 , 28/06/2014

94.73 , 0.00 , 30/09/2014 , 30/09/2014

];

NoConcatenate

load * Resident a

where peek(balanceCurrent)<>balanceCurrent or peek(balanceFuture)<>balanceFuture

order by balanceCurrent, balanceFuture, PostingDate;

DROP Table a;

Re: repetative values.

Anonymous — Mon, 20 Oct 2014 23:13:13 GMT

Hey Massimo, thank you for that.

balanceCurrent & balanceFuture is in the PeriodSummary table, and Posting is in the Invoice table,

should i concatenate them together? or is there a better way around this?

Re: repetative values.

Mon, 20 Oct 2014 23:27:23 GMT

Hi,

How do you link APPeriodSummary table to the data model?

If your tables doesn't join properly (no join means cartesian product) you experience duplicate records.

There is key field between MasterCalendar and APInvoice tables but I don't see any common field with the 3rd table.

Ersen

Re: repetative values.

Anonymous — Tue, 21 Oct 2014 00:11:42 GMT

Hey Ersen.

thank you for your reply.
sorry i have only just pasted parts of the codes in what i had posted.

APPeriodSummary is linked to APInvoice with the 'mySupplier' field, ( which was not on what I've posted earlier )

should the links be created somewhere else?

thanks.

Re: repetative values.

Tue, 21 Oct 2014 20:27:43 GMT

Hi,

Do you have any Join statements to any of the tables providing any of the listed fields?

Or any duplicated key in any associated tables in the final model ?

My guess the data is duplicated in your final data model this is the case 99 percent of the time. Placing distinct in any load statements without knowing the problem may cause you more problems.

Ersen

Re: repetative values.

Anonymous — Tue, 21 Oct 2014 20:33:59 GMT

Be careful using distinct with concatenate functions. It gets weird fast.

For example:

Facts:

Load distinct x,y,z

Resident fact1;

Concatenate(Facts)

Load x,y,z

Resident fact2;

This will continue to use distinct to enforce uniqueness, even if not specified in the second table. Whereas:

Facts:

Load distinct x,y,z

Resident fact1;

Concatenate(Facts)

Load distinct x,y,z

Resident fact2;

No longer guarantees that distinct will be enforced on the second (and subsequent) tables.

Weird.