Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Exclude values in data load

Hi, this is what I currently have in my script and was able to load it fine.  From this data load, I'd like to exclude 2 specific values from the Task Name field.  Can someone assist -- what do I put and where? Thank you.

TimeTracking:

LOAD [Work Name],

     [Work Id],

     [Work Type],

     [Work Status],

     [Task Name],

     right([Task Name],len([Task Name])-5) as AdjName, 

     left([Task Name],5) as QV#,

     [Timesheet Remarks]

FROM

[$(vsPath)Timetracking.xlsx]

(ooxml, embedded labels, table is DATA)Where [Work Name]='Application';

1 Solution

Accepted Solutions
ashfaq_haseeb
Champion III
Champion III

Try like this

TimeTracking:

LOAD [Work Name],

     [Work Id],

     [Work Type],

     [Work Status],

     [Task Name],

     right([Task Name],len([Task Name])-5) as AdjName,

     left([Task Name],5) as QV#,

     [Timesheet Remarks]

FROM

[$(vsPath)Timetracking.xlsx]

(ooxml, embedded labels, table is DATA)Where [Work Name]='Application'

and not match ([Task Name],'Value1','Value2');

Regards

ASHFAQ

View solution in original post

5 Replies
Kushal_Chawda

Something like this

TimeTracking:

LOAD [Work Name],

     [Work Id],

     [Work Type],

     [Work Status],

     [Task Name],

     right([Task Name],len([Task Name])-5) as AdjName,

     left([Task Name],5) as QV#,

     [Timesheet Remarks]

FROM

[$(vsPath)Timetracking.xlsx]

(ooxml, embedded labels, table is DATA)

Where Where [Work Name]='Application'

and not match([Task Name],'Value1','Value2');

sunny_talwar

Do you want to exclude the complete rows that match those two Task Name or do you want it to be called something else?

ashfaq_haseeb
Champion III
Champion III

Try like this

TimeTracking:

LOAD [Work Name],

     [Work Id],

     [Work Type],

     [Work Status],

     [Task Name],

     right([Task Name],len([Task Name])-5) as AdjName,

     left([Task Name],5) as QV#,

     [Timesheet Remarks]

FROM

[$(vsPath)Timetracking.xlsx]

(ooxml, embedded labels, table is DATA)Where [Work Name]='Application'

and not match ([Task Name],'Value1','Value2');

Regards

ASHFAQ

Not applicable
Author

Please give excel sheet and mark it which values u want to eliminate

sasiparupudi1
Master III
Master III

TBLE1:

LOAD [Work Name],

    [Work Id],

    [Work Type],

    [Work Status],

    [Task Name],

    right([Task Name],len([Task Name])-5) as AdjName,

    left([Task Name],5) as QV#,

    [Timesheet Remarks] inline

[

Work Name, Work Id,Work Type,Work Status,Task Name,Timesheet Remarks

Application,1,1,xxx,Task ABC 1,test

Application,2,1,xxx,Task ABC 1,test

Application,3,1,xxx,Task ABC 1,test

Application,4,1,xxx,value1,test

Application,5,1,xxx,Value2,test

Application,6,1,Text1,vaLUe1,test

]

where ([Work Name]='Application' and not (lower([Task Name])=lower('Value1') or lower([Task Name])=lower('Value2')));