Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hkg_qlik
Creator III
Creator III

Distinct (Load)

Hi,

I have a table with the following fields:

MEMBERID

ADDRESS

STATE

COUNTY

EMAIL

I want to remove the duplicate records while script loading process based on distinct(MEMBERID)

Thanks,

H

1 Solution

Accepted Solutions
CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

T1:

LOAD

      MEMBERID,

     FirstValue(ADDRESS) AS ADDRESS,

     FirstValue(STATE) AS STATE,

     FirstValue(COUNTY) AS COUNTY,

     FirstValue(EMAIL) AS EMAIL

FROM BLAH.XLS

GROUP BY MEMBERID ;

View solution in original post

9 Replies
Not applicable

T1:

LOAD DISTINCT MEMBERID

FROM BLAH.XLS

CONCATENATE(T1)

LOAD MEMBERID,

     ADDRESS,

     STATE,

     COUNTY,

     EMAIL

FROM BLAH.XLS

hkg_qlik
Creator III
Creator III
Author

Not working!

MayilVahanan

Hi,

     Try this,

     Load          

     If(Previous(MEMBERID) = MEMBERID, 0, MEMBERID) As MEMBERID,

     ADDRESS,

     STATE,

     COUNTY,

     EMAIL

From [FileName].[FileExtension]

Order By MEMBERID

Hope it helps

Thanks & Regards,
Mayil Vahanan R
hkg_qlik
Creator III
Creator III
Author

not working!

christian77
Partner - Specialist
Partner - Specialist

Hi:

Do just

T1:

LOAD DISTINCT MEMBERID      /////only this field will be distinct or unique.

     ADDRESS,

     STATE,

     COUNTY,

     EMAIL

FROM BLAH.XLS

Have on mind that during the regular select distinct, load dialog box will show many records fetched, but in your table will be correct.

hkg_qlik
Creator III
Creator III
Author

Hi Christian,

I tried that before but it seems to be not working. I am using a QVD file as a source. Does that affect anything?

Thanks,

H

Not applicable

Where we have all said FROM BLAH.XLS that needs to be FROM ..........QVD

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

T1:

LOAD

      MEMBERID,

     FirstValue(ADDRESS) AS ADDRESS,

     FirstValue(STATE) AS STATE,

     FirstValue(COUNTY) AS COUNTY,

     FirstValue(EMAIL) AS EMAIL

FROM BLAH.XLS

GROUP BY MEMBERID ;

lawrenceiow
Creator II
Creator II

Thanks Chris Conejero, this worked perfectly for me. I had been trying the FIRSTVALUE method but kept running out of memory and it was very slow (1.5million records).

Using the LOAD Distinct method was a lot faster, did not run out of memory and did the job exactly as you described:

I already knew I had 1,488,448 records with 6 duplicates (In a Straight table I used Count(MEMBERID) and Count(Distinct MEMBERID) to check)

The LOAD routine stated it loaded 1,488,448 records and then the straight table showed 1,488,442 for both the Count and Count Distinct.

Thank you very much, even though your answer wasn't what hkgandhi was after it was exactly what I needed.