how to convert sql query to qlikview load statement

[Anonymous]

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

[jonathandienst]

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.

[Anonymous]

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

[jonathandienst]

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

;

[Anonymous]

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

[jonathandienst]

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

;

[Anonymous]

as Qlik Community labels you, you really are a guru

much appreciated man.

Cheers!

[jonathandienst]

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

;