topic Re: How to write where clause with not equal in Resident table .? in QlikView

How to write where clause with not equal in Resident table .?

ashmitp869 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

How to write where clause with condition in Resident table in Qlikview .

For eg ,

I have a non user table .

How can I write this with the condition .

In the where clause the deal is the temp_Deal table and dealgrp1 is the dealgrpid .

Please see the script.

"NonUser":

Load ProductListingJoinKey,

supplier as supplierNU,

dealer as dealerNU,

suppliername as suppliernameNU,

dealername as dealernameNU

Resident ProductsListing;

left join(NonUser)

Load ProductListingJoinKey,

MakeDate(Year(date_updated),Month(date_updated)) as DateJoinKeyPL,

dealgroup_id as dealgroup_idNU

Resident temp_Deal ;

//where dealerNU not in (select dealgrp1.institution_id from VAF5.dealgroup dealgrp1 inner join VAF5.deal deal1 on deal1.dealgroup_id = dealgrp1.id);

"dealgrpid":

Load dealgroup_idU,

dealerU;

SQL select dealgroup.id as dealgroup_idU,

dealgroup.institution_id as dealerU

from VAF5.dealgroup;

Re: How to write where clause with not equal in Resident table .?

YoussefBelloum — Tue, 27 Feb 2018 10:05:20 GMT

Hi,

to do the equivalent to this line:

//where dealerNU not in (select dealgrp1.institution_id from VAF5.dealgroup dealgrp1 inner join VAF5.deal deal1 on deal1.dealgroup_id = dealgrp1.id);

you can try this:

dealgrpid:

Load

dealgroup_idU as ID,

dealerU

;

SQL select

dealgroup.id as dealgroup_idU,

dealgroup.institution_id as dealerU

from VAF5.dealgroup;

inner join

Load

Deal1.dealGroup_id as ID,

;

SQL select

From VAF5.deal deal1

"NonUser":

Load

ProductListingJoinKey,

supplier as supplierNU,

dealer as dealerNU,

suppliername as suppliernameNU,

dealername as dealernameNU,

ID //(just verify that you load the ID here with the name ID)

Resident ProductsListing;

left join(NonUser)

Load

ProductListingJoinKey,

MakeDate(Year(date_updated),Month(date_updated)) as DateJoinKeyPL,

dealgroup_id as dealgroup_idNU

Resident temp_Deal

WHERE NOT EXIST(ID);

Re: How to write where clause with not equal in Resident table .?

ashmitp869 — Wed, 28 Feb 2018 06:48:33 GMT

Hi Youssef,

I tried to implement the not in as where not exist but it didnt work.

I am providing my sample model, please kindly check if you can help me.

Please see my MySql Query .

select

distinct dealer.id as dealer,

supplier.id as supplier,

dealer.name as dealername,

supplier.name as suppliername,

Month(date_updated) as month,

Year(date_updated) as year

from VAF5.aux_institutionfspconfig ifspc

inner join VAF5.institution dealer on dealer.id = ifspc.institution2_id

inner join VAF5.institution supplier on supplier.id = ifspc.institution1_id

left join VAF5.deal deal on deal.institution_id = ifspc.institution1_id

#and deal.dealgroup_id in (select dealgrp.id from VAF5.dealgroup dealgrp)

and dealer.id not in (select distinct dealgrp1.institution_id from VAF5.dealgroup dealgrp1 inner join VAF5.deal deal1 on deal1.dealgroup_id = dealgrp1.id

where deal1.institution_id=16742 and deal1.date_updated between '2018-01-01' and '2018-01-31')

where supplier.id=16742

and date_updated between '2018-01-01' and '2018-01-31';

I tried to implement the query in Qlikview script but its not working .

My requirement is

ProductListing - tempDealProductList should acheive the dealer id every period .

Sample model attached.

Re: How to write where clause with not equal in Resident table .?

YoussefBelloum — Wed, 28 Feb 2018 08:28:41 GMT

Just to know, why you want to make all these conditions on the LOAD part, your query don't work ?

Re: How to write where clause with not equal in Resident table .?

ashmitp869 — Wed, 28 Feb 2018 08:48:13 GMT

I dont have permision to do anything on mysql .

So, I have to implement the sql query on my qlikview script.

Re: How to write where clause with not equal in Resident table .?

YoussefBelloum — Wed, 28 Feb 2018 09:27:42 GMT

if you can query with READ only privilege, you will be able to do all tese joins on the where.

did you try ?

Re: How to write where clause with not equal in Resident table .?

ashmitp869 — Wed, 28 Feb 2018 10:13:17 GMT

Yes , my sql query is correct . I need to implement it in qlikview script.

Re: How to write where clause with not equal in Resident table .?

YoussefBelloum — Wed, 28 Feb 2018 10:24:15 GMT

Can you export some rows of each table on an excel file (of different sheets) ?

Re: How to write where clause with not equal in Resident table .?

ashmitp869 — Wed, 28 Feb 2018 10:28:40 GMT

This is my end result in MySql which I need to implement in Qlikview.

Re: How to write where clause with not equal in Resident table .?

YoussefBelloum — Wed, 28 Feb 2018 10:33:22 GMT

Sorry, I need to be able to reload, there s lot of joining on your query, so troobleshoot without reloading and testing will be a waste of time..

if are not allowed to share some rows of your data, try to create some rows with the same structure from every table (at least the field you are using on your script)