topic Avoiding duplicate entries in QlikView

Avoiding duplicate entries

NavinReddy — Fri, 13 May 2016 08:11:51 GMT

Dear experts,

Good Afternoon

if can i get updated data for current month same fact id its creating for multiple entries

how to avoid this issue on script i have many fact-ids having same problem

i want to make it below format

any suggestion please not sure how to resolve this issue

Best Regards,

Niranjan

Re: Avoiding duplicate entries

MK_QSL — Fri, 13 May 2016 08:14:27 GMT

It's not duplicate entries... You can see that there are three owners in first table.

Re: Avoiding duplicate entries

MindaugasBacius — Fri, 13 May 2016 08:20:08 GMT

Apart from what is said for correct data set you should definitely use CrossTable statement.

While loading data from table use this:

Re: Avoiding duplicate entries

NavinReddy — Fri, 13 May 2016 08:21:13 GMT

sorry i made a mistake Owner and Lob is common for all

Regards,

Niranjan

Re: Avoiding duplicate entries

MK_QSL — Fri, 13 May 2016 08:22:42 GMT

Now Tier3 and Tier1 is different for each line.. If you will show it on single line what could be the Tier1 and Tier3 for that line?

Re: Avoiding duplicate entries

NavinReddy — Fri, 13 May 2016 08:26:12 GMT

every time i will pick only current month tier1 and tier3 as base tiers for 3months

Re: Avoiding duplicate entries

MK_QSL — Fri, 13 May 2016 08:31:00 GMT

If you remove tier1 and tier3 column then you will get your desired output.. ( I guess)

Re: Avoiding duplicate entries

NavinReddy — Fri, 13 May 2016 08:32:25 GMT

Hi Mindaugas,

i have tried with crosstable i got the 3months of tier1 and tier3 information

please guide me how to display with current month tiers having all months data

Regards,

niranjan

Re: Avoiding duplicate entries

NavinReddy — Fri, 13 May 2016 08:36:37 GMT

but from data base i will get the tier1 and tier3 columns

Re: Avoiding duplicate entries

MindaugasBacius — Fri, 13 May 2016 10:39:42 GMT

Is it what you are looking for?

Status:
CrossTable(Status, Data, 5)
LOAD FactID, 
     Lob, 
     Owner, 
     Tier3, 
     Tier1, 
     Status, 
     Status1, 
     Status2
FROM

(ooxml, embedded labels, table is Sheet1, filters(
Remove(Col, Pos(Top, 7)),
Remove(Col, Pos(Top, 8)),
Remove(Col, Pos(Top, 9))
));


Date:
CrossTable(Date, Info, 5)
LOAD FactID, 
     Lob, 
     Owner, 
     Tier3, 
     Tier1, 
     [Mar 16], 
     [Feb 16], 
     [Jan 16]
FROM

(ooxml, embedded labels, table is Sheet1, filters(
Remove(Col, Pos(Top, 6)),
Remove(Col, Pos(Top, 7)),
Remove(Col, Pos(Top, 8))
));


tmp:
NoConcatenate
LOAD *
Resident Status
Where Data <> '';


inner Join
LOAD *
Resident Date
Where Info <> '';

DROP Fields Status;
DROP Table Status, Date;

Re: Avoiding duplicate entries

NavinReddy — Fri, 13 May 2016 10:49:33 GMT

Hi Mindaugas,

Thank you so much for your support

exactly same but with this i want to select only Mar 16 Tier1 and Tier3

its helpful for me to develop application

Many Thanks,

Niranjan

Re: Avoiding duplicate entries

MindaugasBacius — Fri, 13 May 2016 10:56:27 GMT

I am not sure if I do understand you correctly but if you just choose from the list you will get only March:

Or do you need to get rid of all the data except March in loading stage?

Re: Avoiding duplicate entries

NavinReddy — Fri, 13 May 2016 11:09:59 GMT

yes exactly i need to get only march 16 tier1 and tier3

Re: Avoiding duplicate entries

MindaugasBacius — Fri, 13 May 2016 11:31:43 GMT

Take a look.

Re: Avoiding duplicate entries

NavinReddy — Fri, 13 May 2016 11:46:38 GMT

Hi Mindausgas,

Thanks again, im looking like the below format

Thanks,

Niranjan

Re: Avoiding duplicate entries

MindaugasBacius — Fri, 13 May 2016 11:57:32 GMT

In your original table the fields Tier3 and Tier1 values differ so there is no way to make such table.

In case you have to change all the values in Tier3 to 3313 and Tier1 to 3397.

Re: Avoiding duplicate entries

NavinReddy — Fri, 13 May 2016 12:01:59 GMT

Fine we can change tier values Tier3 to 3313 and Tier1 to 3397.

if we can make like that table

Re: Avoiding duplicate entries

Sat, 14 May 2016 10:13:33 GMT

If you are looking for that entries correct data set you should definitely use Cross-table statement.

In the prefixes zone and labels as embedded labels and https://goo.gl/fJU04B