Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Left Join Duplicates

I need some assistance please. Loading the following, I get a Table1 with each line duplicated 3 times because the DOCNO appears 3 time in table2. Any idea how I can eliminate the duplicates?

Thank you.

Table1:

LOAD DOCNO,

     COST_AMOUNT,

     ENTRY,

     GLNO,

     MONTH,

     PERIOD,

     PROJNO,

     REF,

     SALE_AMOUNT,

     TRANS_DATE,

     YEAR

FROM

(ooxml, embedded labels, table is GL);

Left Join Table1:

Table2:

LOAD DOCNO,

     ACCNO,

     REP,

     STOCKNO

FROM

(ooxml, embedded labels, table is Stock);

12 Replies
sunny_talwar

May be do applymap instead of join here

Don't join - use Applymap instead

sfatoux72
Partner - Specialist
Partner - Specialist

Hi,

If DOCNO appears 3 time in table2 with same combination for other fields, you must simply add a DISTINCT

Left Join Table1:

Table2:

LOAD DISTINCT DOCNO,

     ACCNO,

     REP,

     STOCKNO

FROM

(ooxml, embedded labels, table is Stock);

Not applicable
Author

Thank you.

Below is my resultant table. You see the DOCNO is the same but it duplicates for each STOCKNO.

sunny_talwar

Which of the three STOCKNO would you like to see in your resultant table?

Not applicable
Author

Thank you for your assistance.

I would like to see:

SERV_INTERFORM with REVENUE = 113 232

100200 with REVENUE = 9 020

110200 with REVENUE = 20 335

SERVE INTERFORM with COST OF SALE = 65 275

100200 with COST OF SALE = 11 742

sunny_talwar

But I guess how did you decide this? I mean we need to understand the logic so that we can provide a solution which might work in all cases. You probably need to provide a sample app or sample data to help us help you

Not applicable
Author

Hi, I would send you the qvw and the excel if I I could work out how to upload it.

sunny_talwar

Just what you need

Uploading a Sample

Not applicable
Author

Please refer to the attached.