Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table with the following fields:
MEMBERID
ADDRESS
STATE
COUNTY
I want to remove the duplicate records while script loading process based on distinct(MEMBERID)
Thanks,
H
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 ;
T1:
LOAD DISTINCT MEMBERID
FROM BLAH.XLS
CONCATENATE(T1)
LOAD MEMBERID,
ADDRESS,
STATE,
COUNTY,
FROM BLAH.XLS
Not working!
Hi,
Try this,
Load
If(Previous(MEMBERID) = MEMBERID, 0, MEMBERID) As MEMBERID,
ADDRESS,
STATE,
COUNTY,
From [FileName].[FileExtension]
Order By MEMBERID
Hope it helps
not working!
Hi:
Do just
T1:
LOAD DISTINCT MEMBERID /////only this field will be distinct or unique.
ADDRESS,
STATE,
COUNTY,
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.
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
Where we have all said FROM BLAH.XLS that needs to be FROM ..........QVD
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 ;
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.