WHERE.... IN......

[Anonymous]

Hello my fellow QlikView friends.  I am wondering how to approach this section of SQL code in QlikView.  I need to re-write it as a LOAD statement:

WHERE (((asn_output_CN.Consign_number) In (SELECT [Consign_number] FROM [asn_output_CN] As Tmp GROUP BY [Consign_number] HAVING Count(*)>1 )))
ORDER BY asn_output_CN.Consign_number;

Any thoughts??  Thank you 

[#2]

I'd do it this way (maybe there's better though):

table:

load * from asn_output_CN.qvd (qvd);

left join(table)

load Consign_number, count(Consign_number) as nb_consign

resident table

group by Consign_number

;

inner join(table)

load distinct Consign_number

resident table

where nb_consign>1

;

drop field nb_consign;

Remains only the lines for which Consign_number has more than 1 entry in the original table.

[swuehl · Accepted solution]

Look into using WHERE EXISTS(Field, expression), i.e. first load your Consign number from asn_output_CN, then

your second table

LOAD ..

FROM ...

WHERE EXISTS([Conseign number], [asn_output_CN.Consign_number]);

[Anonymous]

Like this, swuehl??

table:

LOAD CONSIGN_NUMBER

RESIDENT asn_output_CN;

LOAD SERVICE,

MODE,

Cartons,

CBM,

UNITS,

ACTUAL_WT,

CHARGEABLE_WT,

TERMS,

ORIGIN,

COUNTRY_CODE,

COUNTRY,

SUMOFWAR_RISK,

SUMOFFUEL_SURCH,

SUMOFFREIGHT_CHARGES,

SUMOFFOB_PICKUP,

SUMOFOTHER_COSTS,

SUMOFAIRFREIGHT,

SUMOFEXTRA_DEPART,

SUMOFSEA_FREIGHT_95X_CAF,

SUMOFCUSTOMS_CLEARANCE,

SUMOFHANDLING_STA_TAXES,

SUMOFTRANSFER_APT_WH,

SUMOFSTORAGE_FEE,

SUMOFHANDLING_CHARGES,

SUMOFEXTRA_CUSTOM_CLEARANCE,

SUMOFDELIVERY_TO_CAMPEGINE,

SUMOFOTHERS,

SUMOFFREIGHT_COST,

SUMOFEXTRA_COST,

SUMOFTOTAL_COST

FROM

WHERE EXISTS(CONSIGN_NUMBER, asn_output_CN.Consign_number);

[#5]

You can try like below: Lets assume data source are qvd's

TEMP_CONSIGNNO:

LOAD DISTINCT Consign_number

FROM [asn_output_CN.qvd] (qvd)

Where Count(Consign_number) Group By Consign_number ;

TEMP_MAIN:

LOAD * FROM SOURCEMAINQVD.qvd (qvd)

Where Exists(Consign_number) ; // This statement only Consign_number exists in the TEMP_CONSIGNNO

DROP Table TEMP_CONSIGNNO ;

MAIN:

NOCONCATENATE

LOAD * RESIDENT TEMP_MAIN Order BY Consign_number ; // For Order by

DROP Table TEMP_MAIN ;

[#6]

If you have the Consign_number values list you can try with Match function

Where Match(Consign_number , 100, 102, 140 ) ;

[Anonymous]

This is my actual SQL statement:

SELECT asn_output_CN.Consign_number, asn_output_CN.Service, asn_output_CN.Mode, asn_output_CN.Cartons, asn_output_CN.CBM, asn_output_CN.Units, asn_output_CN.Actual_wt, asn_output_CN.Chargeable_wt, asn_output_CN.Terms, asn_output_CN.Origin, asn_output_CN.Country_code, asn_output_CN.Country, asn_output_CN.SumOfWar_risk, asn_output_CN.SumOfFuel_surch, asn_output_CN.SumOfFreight_charges, asn_output_CN.[SumOfFob-pick_up], asn_output_CN.SumOfOther_costs, asn_output_CN.SumOfAirfreight, asn_output_CN.SumOfextra_depart, asn_output_CN.SumOfSea_freight_95X_CAF, asn_output_CN.SumOfCustoms_clearance, asn_output_CN.[SumOfHandling-s-t-a_taxes], asn_output_CN.[SumOfTransfer_apt-wareho], asn_output_CN.SumOfStorage_fee, asn_output_CN.SumOfHandling_charges, asn_output_CN.SumOfExtra_custom_clearance, asn_output_CN.SumOfDelivery_to_Campegine, asn_output_CN.SumOfOthers, asn_output_CN.SumOffreight_cost, asn_output_CN.SumOfextra_cost, asn_output_CN.SumOfTotal_cost

FROM asn_output_CN

WHERE (((asn_output_CN.Consign_number) In (SELECT  FROM  As Tmp GROUP BY  HAVING Count(*)>1 )))

ORDER BY asn_output_CN.Consign_number;

[#8]

Try with Exists function if your source is DB like explained above.