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

[ashmitp869]

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;

[YoussefBelloum]

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

[ashmitp869]

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.

[YoussefBelloum]

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

[ashmitp869]

I dont have permision to do anything on mysql .

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

[YoussefBelloum]

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

did you try ?

[ashmitp869]

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

[YoussefBelloum]

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

[ashmitp869]

!

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

[YoussefBelloum]

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)