Applymap and tmp table

[#1]

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;

[rubenmarin]

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.

[#3]

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.

[rubenmarin]

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)'

[tamilarasu]

Hi George,

Check the below link. You might get some idea.

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

[#6]

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;

[rubenmarin]

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.

[#8]

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'

[t_chetirbok]

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;

[rubenmarin]

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'