Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
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;

18 Replies
Not applicable
Author

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
Creator III
Creator III

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

stabben23
Partner - Master
Partner - Master

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 '

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

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

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

Not applicable
Author

My table only consists 2 fields.

Not applicable
Author

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
Creator III
Creator III

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

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

Not applicable
Author

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