Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
you should use '*' in your conditions instead of '%'
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);
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')
My table only consists 2 fields.
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.
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;
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 ...
Thank you all for your help. The problem is fixed.