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;

[#2]

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

[t_chetirbok]

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

[stabben23]

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

[rubenmarin]

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

[#6]

My table only consists 2 fields.

[#7]

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:

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;

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.

[t_chetirbok]

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;

[rubenmarin · Accepted solution]

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

[#10]

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