Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

thnkgreen
Not applicable

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

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 

1 Solution

Accepted Solutions
swuehl
Not applicable

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

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

7 Replies
Not applicable

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

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
Not applicable

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

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

thnkgreen
Not applicable

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

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

Not applicable

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

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 ;

Not applicable

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

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

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

thnkgreen
Not applicable

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

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;

Not applicable

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

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