field valie map

[soniasweety]

Hi all,

Sasidhar Parupudi

Marco Wedel

I need to create a new field from two tables

TableA:

Exid	first_name	last_name	email
1	ranu	gopi	sss@gm
2	dgdra	jdsg	fgdg@gm
3	gddg	gz	fdd@gm
4	dgdra	g	fdd@gm

TableB:

Exid	DestId	Value
1	AFSC	MALE
1		SALES
2		MALE
2		MEDICINE
3		MALE
3		COMPUTER

Result:

Exid	first_name	last_name	email	AFSC	Gender
1	ranu	gopi	sss@gm	SALES	MALE
2	dgdra	jdsg	fgdg@gm	MEDICINE	MALE
3	gddg	gz	fdd@gm	COMPUTER	MALE
4	dgdra	g	fdd@gm

[soniasweety]

  can you look into    this Marco Wedel

[MarcoWedel]

Hi,

can you describe the relation between "DestId" and "Value" in TableB?

What about the missing DestId values, what's the rationale to fill this column?

Are there other DestIds than "AFSC"?

Besides these questions, what comes to mind is a generic load of TableB:
https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes...

regards

Marco

[vishsaggi]

Looks pretty doable. Provided if you answer marcowedel‌ questions I am sure he will or we will.

[vishsaggi · Accepted solution]

I am sure there is definitely an easier way to do this. Just drafted one way of doing this ignoring your DestID field.

If you can give us how DestId is used in your table. The below script might change.

TableB:
LOAD * INLINE [
Exid, DestId, Value
1, AFSC, MALE
1, ,SALES
2, ,MALE
2, ,MEDICINE
3, ,MALE
3, ,COMPUTER
];

NoConcatenate
TableC:
LOAD *, IF(Subfield(NewValue, '|', 1) = 'MALE', Subfield(NewValue, '|',1),
IF(Subfield(NewValue, '|', 2) = 'MALE', Subfield(NewValue, '|',2))) AS Gender,
IF(Subfield(NewValue, '|', 1) <> 'MALE', Subfield(NewValue, '|',1),
IF(Subfield(NewValue, '|', 2) <> 'MALE', Subfield(NewValue, '|',2))) AS AFSC;
LOAD Exid, Concat(Value, '|') AS NewValue
Resident TableB
Group By Exid;


Drop Table TableB;

Join
MapTab1:
LOAD * INLINE [
Exid, first_name, last_name, email
1, ranu, gopi, sss@gm
2, dgdra, jdsg, fgdg@gm
3, gddg, gz, fdd@gm
4, dgdra, g, fdd@gm
];