Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Applymap and tmp table

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;

1 Solution

Accepted Solutions

Re: Applymap and tmp table

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

18 Replies

Re: Applymap and tmp table

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.

Not applicable

Re: Applymap and tmp table

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

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

Hi George,

Check the below link. You might get some idea.

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

Not applicable

Re: Applymap and tmp table

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

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.

Not applicable

Re: Applymap and tmp table

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
Valued Contributor

Re: Applymap and tmp table

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

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'

Community Browser