Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Add/remove detial to field

Hi,

We are having a field as supplierid like 'ACD-2464788',DBG-6437665555,CND-765322456 and so on.

Here we need to find only supplierid start with ACD and DBG and after that we need to add -1156 for ACDsupplier at end and -4324 at end

The final output will look like

Supplier ACD:

2464788-1156

Supplier DBG:

6437665555-4324

Thanks..

1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Try this

load  if(left(supplierid ,3) = 'ACD'

,mid(supplierid ,5)&'-1156'

,mid(supplierid ,5)&'-4324') as newsupplierid

From datatable

where left(supplierid ,3) = 'ACD' or left(supplierid ,3) = 'DBG' ;


regards

View solution in original post

9 Replies
Highlighted
Creator III
Creator III

Try this

load  if(left(supplierid ,3) = 'ACD'

,mid(supplierid ,5)&'-1156'

,mid(supplierid ,5)&'-4324') as newsupplierid

From datatable

where left(supplierid ,3) = 'ACD' or left(supplierid ,3) = 'DBG' ;


regards

View solution in original post

Highlighted
Creator II
Creator II

you can try using wild card in the script

Highlighted

Perhaps this? I haven't test this due to software not installed in my client space

Table_Name:

Load FieldName, SubField(FieldName, '-', 1) as First_FieldName,

SubField(FieldName, '-', 2) as Second_FieldName

Where WildMatch(FieldName, 'ACD*', 'DBG*');

Load * Inline [

FieldName

ACD-2464788

DBG-6437665555

CND-765322456

];

Final:

NoConcatenate

Load FieldName, First_FieldName, Second_FieldName, Pick(Match(First_FieldName, 'ACD', 'DBG'), Second_FieldName & '-1156', Second_FieldName & '-4324') as FieldName_Copy

Resident Table_Name;

Drop Field FieldName;

Rename Field FieldName_Copy to FieldName;

Drop Table Table_Name;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator III
Creator III

Any other approach.

Thanks..

Highlighted
Master II
Master II

Hi

anil's approach should work. Did you try it ? If you are not satisfied kindly share some sample data with output which will help us help you .

Learning never stops.
Highlighted

I've seen atleast more than 20 threads who owner of thread. Fortunately, you never come up with the end up result where you need to get So finally, When you say second approach i would ask to explain little more

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator III
Creator III

Hi,

I have checked it but it give only 'FieldName' which is in Inline.

Thanks..

Highlighted
Master II
Master II

Add your table in place of inline table in the preceding load.

Inline is the sample data he has taken.

regards

Pradosh

Learning never stops.
Highlighted

Working copy

Capture.PNG

Note - Please mark correct answer for exact result and mark as helpful for others ..

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)