topic Re: field valie map in QlikView

field valie map

soniasweety — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

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

Re: field valie map

soniasweety — Tue, 03 Apr 2018 20:17:23 GMT

can you look into this Marco Wedel

Re: field valie map

MarcoWedel — Tue, 03 Apr 2018 20:32:55 GMT

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/Generic.htm

regards

Marco

Re: field valie map

vishsaggi — Tue, 03 Apr 2018 20:42:21 GMT

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

Re: field valie map

vishsaggi — Tue, 03 Apr 2018 20:52:32 GMT

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
];