Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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]);
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.
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]);
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);
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 ;
If you have the Consign_number values list you can try with Match function
Where Match(Consign_number , 100, 102, 140 ) ;
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;
Try with Exists function if your source is DB like explained above.