topic Re: WHERE.... IN...... in QlikView

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

Anonymous — Mon, 13 Jan 2014 20:06:24 GMT

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

Re: WHERE.... IN......

Mon, 13 Jan 2014 20:19:44 GMT

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.

Re: WHERE.... IN......

swuehl — Mon, 13 Jan 2014 20:22:06 GMT

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]);

Re: WHERE.... IN......

Anonymous — Mon, 13 Jan 2014 20:27:22 GMT

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);

Re: WHERE.... IN......

Mon, 13 Jan 2014 20:33:05 GMT

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 ;

Re: WHERE.... IN......

Mon, 13 Jan 2014 20:34:16 GMT

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

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

Re: WHERE.... IN......

Anonymous — Mon, 13 Jan 2014 20:34:34 GMT

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;

Re: WHERE.... IN......

Mon, 13 Jan 2014 20:48:51 GMT

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