Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I have the following sql query:
sql select a.sk_customer_no,
a.cellphone
from (select sk_customer_no,
cellphone,
row_number() over(partition by sk_customer_no, sk_cust_type_no order by eff_start_date desc) as seq
from fct_cellphone
where sk_cust_type_no = 3) a
where seq = 1;
Now what I want to do is to change this so I read in the csv for fct_cellphone instead of using the query.
My load looks as follow:
LOAD
cellphone,
eff_start_date,
sk_customer_no,
sk_cust_type_no
FROM fct_cellphone.csv;
how would my load statement look like to give me the same results as the sql query?
Thanks,
Stefan
I assume that the query is trying to get the most recent mobile number for a customer. This should work in QV. The first part gets the most recent date for each customer and the join adds the [cellphone] for the most recent date:
LOAD
sk_customer_no,
max(eff_start_date) As eff_date
FROM fct_cellphone.txt (....)
WHERE sk_cust_type_no = 3
GROUP BY sk_customer_no;
INNER JOIN
LOAD
sk_customer_no,
cellphone,
eff_start_date As eff_date
FROM fct_cellphone.txt (....)
WHERE sk_cust_type_no = 3;
//Optional - your SQL query did not return this field
DROP FIELD eff_date;
Adjust the FROM clause to the correct form for your data source.
Hi Jonathan, thank you so much for your feedback, it worked perfectly... kind of..
on that table there is some cellphone numbers for the same customer that has the same effective date.
what the query does is to just bring back the first record that it finds.
in qlikview, because some customers have multiple cellphone numbers for the same effective date, it brings back more than one record.
I know there is a way to return the first value in qlikview, but Im rather going to inform the database developers to fix their stuff
I will inform you what my solution was.
All the best
Or do this:
LOAD
sk_customer_no,
Concat(cellphone, ',') As cellphone,
eff_start_date As eff_date
FROM fct_cellphone.txt (....)
WHERE sk_cust_type_no = 3
GROUP BY sk_customer_no, eff_start_date
;
Hi Jonathan,
I got a similar query that looks as follows:
select sk_source_subsidiary_no,
sk_cust_no,
sk_cust_prod_no,
approve_status_ind
from
(select sk_source_subsidiary_no,
sk_cust_no,
sk_cust_prod_no,
approve_status_ind,
row_number () over (partition by sk_cust_prod_no,sk_cust_no order by create_date desc) seq
from latest_cust_product_view)
where seq = 1 ;
So Basically I'm returning the source_subsidiary, cust_no, prod_no, and approve_status for the max create_date.
Could you maybe help me in changing this query to by qlikview syntax?
Thanks
Use the same logic as before, just change the field and table names:
LOAD sk_source_subsidiary_no,
sk_cust_no,
sk_cust_prod_no,
approve_status_ind,
Max(create_date) As create_date
FROM latest_cust_product_view.txt (....)
GROUP BY sk_source_subsidiary_no,
sk_cust_no,
sk_cust_prod_no,
approve_status_ind
;
INNER JOIN
LOAD sk_source_subsidiary_no,
sk_cust_no,
sk_cust_prod_no,
approve_status_ind,
create_date
FROM latest_cust_product_view.txt (....)
;
as Qlik Community labels you, you really are a guru
much appreciated man.
Cheers!
I think this will give the same result:
LOAD sk_source_subsidiary_no,
sk_cust_no,
sk_cust_prod_no,
approve_status_ind,
Max(create_date) As create_date
FROM latest_cust_product_view.txt (....)
GROUP BY sk_source_subsidiary_no,
sk_cust_no,
sk_cust_prod_no,
approve_status_ind
;