Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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);
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.
Just to know, why you want to make all these conditions on the LOAD part, your query don't work ?
I dont have permision to do anything on mysql .
So, I have to implement the sql query on my qlikview script.
if you can query with READ only privilege, you will be able to do all tese joins on the where.
did you try ?
Yes , my sql query is correct . I need to implement it in qlikview script.
Can you export some rows of each table on an excel file (of different sheets) ?
!
This is my end result in MySql which I need to implement in Qlikview.
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)