Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I need to create a new field from two tables
TableA:
Exid | first_name | last_name | |
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 | 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 |
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
];
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
Looks pretty doable. Provided if you answer marcowedel questions I am sure he will or we will.
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
];