topic Re: how to convert sql query to qlikview load statement in QlikView

how to convert sql query to qlikview load statement

Anonymous — Thu, 13 Aug 2015 11:25:57 GMT

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

Re: how to convert sql query to qlikview load statement

jonathandienst — Thu, 13 Aug 2015 12:15:58 GMT

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.

Re: how to convert sql query to qlikview load statement

Anonymous — Thu, 13 Aug 2015 14:27:17 GMT

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

Re: how to convert sql query to qlikview load statement

jonathandienst — Thu, 13 Aug 2015 14:35:14 GMT

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

;

Re: how to convert sql query to qlikview load statement

Anonymous — Wed, 19 Aug 2015 06:43:15 GMT

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

Re: how to convert sql query to qlikview load statement

jonathandienst — Wed, 19 Aug 2015 06:52:25 GMT

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 (....)

;

Re: how to convert sql query to qlikview load statement

Anonymous — Wed, 19 Aug 2015 06:54:30 GMT

as Qlik Community labels you, you really are a guru

much appreciated man.

Cheers!

Re: how to convert sql query to qlikview load statement

jonathandienst — Wed, 19 Aug 2015 07:28:06 GMT

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

;