topic Re: Applymap and tmp table in QlikView

Applymap and tmp table

Fri, 20 Nov 2015 09:48:01 GMT

Hi all.

I use applymap to create a tmp table.

Then i want to filter that data using select statements.

I dont know how to replace Transactions table with my tmp table!!!

Pls help.

Below is a sample of my script:

//Transfer user names to Transactions

MapTelephoneExtToUsers:

Mapping

LOAD * FROM

[New folder\New folder\USERS.xlsx]

(ooxml, embedded labels, table is Sheet1);

//Create temp table containing all fields

TransactionUsers:

Load *,

ApplyMap ('MapTelephoneExtToUsers',Ext, null() ) as NUsers;

sql select * from Transactions;

MinMaxTable:

Load *;

SQL SELECT MIN(DATE) AS minDate, MAX(DATE) AS maxDate from Transactions;

let vMinDate = peek('minDate',0,'MinMaxTable');

let vMaxDate = peek('maxDate',0,'MinMaxTable');

Q1_TOTAL_TRANSACTIONS_NUMBER:

SELECT ext, COUNT(ext) AS INTERNATIONAL FROM Transactions WHERE (date BETWEEN #$(MinDate)# AND #$(MaxDate)#) AND ( TRIM(number_dialed) LIKE '+_%' OR TRIM(number_dialed) LIKE '0_%') AND status = 'O' AND cost <> 0.0 GROUP BY ext;

Outer Join (Q1_TOTAL_TRANSACTIONS_NUMBER)

LOAD *;

SELECT ext, COUNT(ext) AS MOBILE FROM Transactions WHERE (date BETWEEN #$(MinDate)# AND #$(MaxDate)#) AND ( TRIM(number_dialed) LIKE '9%') AND status = 'O' AND cost <> 0.0 GROUP BY ext;

Re: Applymap and tmp table

rubenmarin — Fri, 20 Nov 2015 10:20:17 GMT

Hi George, maybe is only a typo in the post but your variables are named 'vMinDate' and 'vMaxDate', in the SELECT you're using 'Mindate' and 'MaxDate' (without the preceding 'v').

Also check in the reload log how is constructing the final SELECT, maybe the variables needs a Date() funtion to format the date.

Re: Applymap and tmp table

Fri, 20 Nov 2015 10:27:28 GMT

Thank you Ruben.

The date function works fine.

My problem is that i want to retrieve the data further down from the tmp table (TransactionUsers) instead of the Transactions table but i don't know how to do it.

Re: Applymap and tmp table

rubenmarin — Fri, 20 Nov 2015 11:10:37 GMT

Maybe you asking about 'Resident' load?. Resident is used to load from a previously loaded table in QV:

test:

LOAD * Resident TransactionUsers Where DateField>='$(vMindate)' and DateField<='$(vMaxDate)'

Re: Applymap and tmp table

tamilarasu — Fri, 20 Nov 2015 11:28:17 GMT

Hi George,

Check the below link. You might get some idea.

http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

Re: Applymap and tmp table

Fri, 20 Nov 2015 11:53:02 GMT

Ok i will take things from the start because i haven't explained well what i need .

I have a table named "Transactions" and my script works perfectly when i am using it.

All i want to do now is add a column to that table using applymap and use the new table in my script.

Ex. I have now:

Q1_TOTAL_TRANSACTIONS_NUMBER:

SELECT ext, COUNT(ext) AS INTERNATIONAL FROM Transactions WHERE (date BETWEEN #$(MinDate)# AND #$(MaxDate)#) AND ( TRIM(number_dialed) LIKE '+_%' OR TRIM(number_dialed) LIKE '0_%') AND status = 'O' AND cost <> 0.0 GROUP BY ext;

And i want to do:

Q1_TOTAL_TRANSACTIONS_NUMBER:

SELECT ext, COUNT(ext) AS INTERNATIONAL FROM TransactionUsers WHERE (date BETWEEN #$(MinDate)# AND #$(MaxDate)#) AND ( TRIM(number_dialed) LIKE '+_%' OR TRIM(number_dialed) LIKE '0_%') AND status = 'O' AND cost <> 0.0 GROUP BY ext;

Re: Applymap and tmp table

rubenmarin — Fri, 20 Nov 2015 12:50:17 GMT

Have you tried with Resident like I said before?

Q1_TOTAL_TRANSACTIONS_NUMBER:

LOAD ext, COUNT(ext) AS INTERNATIONAL

Resident TransactionUsers

WHERE DateField>='$(vMindate)' and DateField<='$(vMaxDate)' AND ( Left(TRIM(number_dialed), 2)='+_' OR Left(TRIM(number_dialed), 2)='0_') AND status = 'O' AND cost <> 0.0 GROUP BY ext;

All the fields used in 'Where' have to be loaded in TransactionUsers table.

Re: Applymap and tmp table

Fri, 20 Nov 2015 14:36:37 GMT

i have tried the script you sent me but it gives me the following error:

Field not found - <date>

MinMaxTable:

LOAD * Resident TransactionUsers Where date>='' and date<='18/11/2015'

Re: Applymap and tmp table

t_chetirbok — Mon, 23 Nov 2015 07:48:24 GMT

Hello!

try this script

//Create temp table containing all fields

TransactionUsers:

Load *,

ApplyMap ('MapTelephoneExtToUsers',Ext, null() ) as NUsers;

sql select * from Transactions;

MinMaxTable:

Load MIN(DATE) AS minDate, MAX(DATE) AS maxDate Resident TransactionUsers;

let vMinDate = peek('minDate',0,'MinMaxTable');

let vMaxDate = peek('maxDate',0,'MinMaxTable');

noconcatenate:

Q1_TOTAL_TRANSACTIONS_NUMBER:

load ext, COUNT(ext) AS INTERNATIONAL Resident TransactionUsers

WHERE (DATE <= $(vMinDate) AND DATE>=$(vMaxDate)) AND ( TRIM(number_dialed) LIKE '+*' OR TRIM(number_dialed) LIKE '0*') AND status = 'O' AND cost <> 0.0

GROUP BY ext;

Outer Join (Q1_TOTAL_TRANSACTIONS_NUMBER)

load ext, COUNT(ext) AS MOBILE Resident TransactionUsers

WHERE (DATE <= $(vMinDate) AND DATE>=$(vMaxDate)) AND ( TRIM(number_dialed) LIKE '9*') AND status = 'O' AND cost <> 0.0

GROUP BY ext;

drop table TransactionUsers;

Re: Applymap and tmp table

rubenmarin — Mon, 23 Nov 2015 08:25:43 GMT

It's telling that the 'date' field doesn't exists, check if this is exactly the name of the field. Field name are case sensitive so 'date'<>'Date'<>'DATE'

Re: Applymap and tmp table

Mon, 23 Nov 2015 14:07:10 GMT

Hi Ruben thanks for all the help. You were wright about the field,it was case sensitive. Now my problem is when i run the script it gives no errors but it doesn't fetch a line.

Ex:

Connected

MapTelephoneExtToUsers << Sheet1 76 lines fetched

TransactionUsers << Transactions 289.470 lines fetched

MinMaxTable << TransactionUsers 1 lines fetched

Q1_TOTAL_TRANSACTIONS_NUMBER << TransactionUsers 0 lines fetched

TransactionUsers 0 lines fetched

Below is my script:

MapTelephoneExtToUsers:

Mapping

LOAD *

FROM

[qliktest\New folder\USERS.xlsx]

(ooxml, embedded labels, table is Sheet1);

TransactionUsers:

Load *,

ApplyMap ('MapTelephoneExtToUsers',Ext, null() ) as NUsers;

sql select * from Transactions;

MinMaxTable:

LOAD MIN(Date) AS minDate, MAX(Date) AS maxDate Resident TransactionUsers;

let vMinDate = peek('minDate',0,'MinMaxTable');

let vMaxDate = peek('maxDate',0,'MinMaxTable');

Q1_TOTAL_TRANSACTIONS_NUMBER:

LOAD Ext, COUNT(Ext) AS INTERNATIONAL

Resident TransactionUsers

WHERE Date>=$(MinDate) AND Date<=$(MaxDate) AND ( Left(TRIM(Number_Dialed), 2)='+_' OR Left(TRIM(Number_Dialed), 2)='0_') AND Status = 'O' AND Cost <> 0.0 GROUP BY Ext;

Outer Join (Q1_TOTAL_TRANSACTIONS_NUMBER)

LOAD Ext, COUNT(Ext) AS MOBILE

Resident TransactionUsers

WHERE Date>=$(MinDate) AND Date<=$(MaxDate) AND ( TRIM(Number_Dialed) LIKE '9%') AND Status = 'O' AND Cost <> 0.0 GROUP BY Ext;

MinDate and MaxDate get whatever value i choose in the calendar so their working proberly, the problem occurs when i try to filter the Resident table

Re: Applymap and tmp table

t_chetirbok — Mon, 23 Nov 2015 17:14:56 GMT

you should use '*' in your conditions instead of '%'

Re: Applymap and tmp table

stabben23 — Mon, 23 Nov 2015 19:10:49 GMT

Hi,

Mapping Load takes two fields and only two, you have to define the keyfiled and the field you want to map, like this.

MapTelephoneExtToUsers:

Mapping

LOAD

Keyfield,

Valuefield

FROM

[qliktest\New folder\USERS.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Applymap and tmp table

rubenmarin — Tue, 24 Nov 2015 08:18:07 GMT

Hi, in your '20-nov-2015 15:36' post seems that QV doesn't have a value in MinDate (it shows "Where date>='' and date<='18/11/2015')"

Check in the reload log if the script is retrieving the dates stored in the variables.

Also add a simple quote before and after the variable:

- $(MinDate) --> '$(MinDate)'

- $(MaxDate) --> '$(MaxDate)'

- (TRIM(Number_Dialed) LIKE '9%') --> (Left(TRIM(Number_Dialed),1)='9')

Re: Applymap and tmp table

Tue, 24 Nov 2015 10:18:20 GMT

My table only consists 2 fields.

Re: Applymap and tmp table

Tue, 24 Nov 2015 10:32:14 GMT

MinDate and MaxDate variables get whatever value i select in the calendar of qlikview so their working correctly. I tested them many times.

I also tried the syntax you recommended in your last post but the problem still remains the same.

The difference from previously is that now i filter the resident table (which is Transaction table + one extra column) instead Transactions table.

BEFORE:

For the date 13/11/2015 it fetches 27 lines.

NOW:

LOAD Ext, COUNT(Ext) AS INTERNATIONAL

Resident TransactionUsers

WHERE Date>='$(MinDate)' AND Date<='$(MaxDate)' AND ( Left(TRIM(Number_Dialed), 2)='+_' OR Left(TRIM(Number_Dialed), 2)='0_') AND Status = 'O' AND Cost <> 0.0 GROUP BY Ext;

For the date 13/11/2015 it fetches 0 lines.

"" ( Left(TRIM(Number_Dialed), 2)='+_' OR Left(TRIM(Number_Dialed), 2)='0_') ""

When i remove the script above it fetches 40 lines. So the problem must be in that part of the script.

Re: Applymap and tmp table

t_chetirbok — Tue, 24 Nov 2015 11:18:48 GMT

Hi, try this

Q1_TOTAL_TRANSACTIONS_NUMBER:

load ext, COUNT(ext) AS INTERNATIONAL Resident TransactionUsers

WHERE (DATE <= $(vMinDate) AND DATE>=$(vMaxDate)) AND ( TRIM(number_dialed) LIKE '+*' OR TRIM(number_dialed) LIKE '0*') AND status = 'O' AND cost <> 0.0

GROUP BY ext;

Join (Q1_TOTAL_TRANSACTIONS_NUMBER)

load ext, COUNT(ext) AS MOBILE Resident TransactionUsers

WHERE (DATE <= $(vMinDate) AND DATE>=$(vMaxDate)) AND ( TRIM(number_dialed) LIKE '9*') AND status = 'O' AND cost <> 0.0

GROUP BY ext;

Re: Applymap and tmp table

rubenmarin — Tue, 24 Nov 2015 12:57:05 GMT

If it's only that line check if there is records in TransactionUsers with values in Number_Dialed starting by '+_' or '0_'.

you can also try wildmatch:

Where ... and WildMatch(Trim(Number_Dialed), '+_*', '0_*') and ...

Re: Applymap and tmp table

Fri, 04 Dec 2015 14:49:15 GMT

Thank you all for your help. The problem is fixed.