Joining tables but after field manipulations

[Rsaiq]

Hi All,

I have 2 table as sales and quote like below,need to do left join after field manipulations in qlikview

Sales:

Id Amount

1 100

2 200

3 300

4 400

5 500

G6 600

YY7 700

UUU8 800

Z9 900

EEEEEE10 1000

UUUU11 1100

12 1200

13 1300

N14 1400

Quote:

Id Amount New

1 200

2 300

3 400

4 500

5 600

6 700

7 800

8 900

9 1000

10 1100

11 1200

12 1300

13 1400

15 1500

I need to add characters same as id of sales into id of quote first and then need to do left join and result should like below

Id Amount Amount New

1 100 200

2 200 300

3 300 400

4 400 500

5 500 600

G6 600 700

YY7 700 800

UUU8 800 900

Z9 900 1000

EEEEEE10 1000 1100

UUUU11 1100 1200

12 1200 1300

13 1300 1400

N14 1400 -

Please help me to get this resolve.

Thanks in advance

[G3S]

Try:

NewTable:

Join (Quote)

Load

Id,

Amount

Resident  Sales;

[vinieme12]

As below

Sales:

Load ID,Amount

From XyzSource;

Left Join(Sales)

Load   keepchar(SalesID,'0123456789) as ID   , SalesID , NewAmount

From SomeOtherSource; 

[Rsaiq]

Hi,

On above script using keepchar, it will remove characters but I don't want remove characters from id from sales table 

I want to add characters in quote id by looking with sales id.

Thanks

[vinieme12]

wrong table names, just flip the joins

Sales:

Load ID as SalesID,    keepchar(SalesID,'0123456789) as ID , Amount

From SalesData;

Left Join(Sales)

Load   ID, NewAmount

From QuotesData;

[Rsaiq]

Hi,

Thank you for your reply

You are removing the characters from sales table that I don't need to do.

I want to add same characters which are present in sales id into quote id.

Taking Eg for 1 value I.e row number 6, value is G6 In sales id

In quoteid , value is 6.

So on your code it will remove G from salesid and both id will be mapped as 6 but I don't want to do that

My requirement is vice-versa

Instead of removing G from salesid I need to add G in Quoteid so my updated data should be like below

Sales id

G6

Quote id

G6

Hope you understand my requirement.

Thanks 

[sidhiq91]

@Rsaiq Please see the below code:

NoConcatenate
Sales:
Load *,
if(IsText(Id),1,0) as Text_Field
inline [

Id, Amount

1, 100

2, 200

3, 300

4, 400

5, 500

G6, 600

YY7, 700

UUU8, 800

Z9, 900

EEEEEE10, 1000

UUUU11, 1100

12, 1200

13, 1300

N14, 1400];

NoConcatenate

Quote:

Load Id,
Amount_New
inline [

Id, Amount_New

1, 200

2, 300

3, 400

4, 500

5, 600

6, 700

7, 800

8, 900

9, 1000

10, 1100

11, 1200

12, 1300

13, 1400

15, 1500];

Concatenate

Load Id,
Amount as Amount_New
Resident Sales
where Text_Field=1;

Drop Field Text_Field;

Left join(Quote)

Load Id, Amount
Resident Sales ;

Drop table Sales;

Exit Script;

[Rsaiq]

Hi,

Thanks for your reply.

I am looking to manipulate the quote id by adding same characters as sales id.

I don't need to do any changes in sales table ,sales table should be as it is.

Just need to make changes in quote Id by adding the same characters as sales id.

Thanks

[Mark_Winter]

Sales:
load Id as SalesId,Amount;
load * inline [
Id, Amount
1 ,100
2 ,200
3 ,300
4 ,400
5 ,500
G6 ,600
YY7 ,700
UUU8, 800
Z9 ,900
EEEEEE10, 1000
UUUU11, 1100
12 ,1200
13 ,1300
N14, 1400];

Quote:
load Id,[Amount New];
load * inline [
Id, Amount New
1, 200
2, 300
3, 400
4, 500
5, 600
6, 700
7, 800
8, 900
9, 1000
10, 1100
11, 1200
12, 1300
13, 1400
15, 1500];

EndTable:
load PurgeChar(SalesId,'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as Id,SalesId,Amount
resident Sales;
drop table Sales;
left join (EndTable)
load * resident Quote;
drop table Quote;
drop field Id;
rename field SalesId to Id;

[QFabian · Accepted solution]

Hi @Rsaiq  here my option. very similar to the others 😉

Sales_Aux:
Load * INLINE [
Id, Amount
1, 100
2, 200
3, 300
4, 400
5, 500
G6, 600
YY7, 700
UUU8, 800
Z9, 900
EEEEEE10, 1000
UUUU11, 1100
12, 1200
13, 1300
N14, 1400
];

Quote_Aux:
Load * INLINE [
Id, Amount New
1, 200
2, 300
3, 400
4, 500
5, 600
6, 700
7, 800
8, 900
9, 1000
10, 1100
11, 1200
12, 1300
13, 1400
15, 1500
];

Sales:
Load
Id,
Amount,
keepchar(Id,'0123456789') as Id_Sales
Resident Sales_Aux;
drop table Sales_Aux;

left join
Load
Id as Id_Sales,
[Amount New]

Resident Quote_Aux;
drop table Quote_Aux;