Load Script Question

[brf10043]

Guys,

I have two tables that I'm loading.  Table A contains details including a Commodity Code.  Table B contains a list of Valid Commodity Codes and a column called Valid? with a value of "Yes".  At the moment, I'm doing a simple join on the Commodity code and simply adding the Valid Column so that table A now has a "Yes" to flag all the rows with Valid Commodity codes.  So the list box on the Valid Column on shows the value of "Yes".  With this simplistic method the Valid? column is either null or "Yes" and the List box on shows the Yes.  How do I get a "No" value in the Valid column and list box?

Thoughts? 

[brf10043]

If it's helpful,  Here is my current Simple load script

Data:
LOAD [Request Id],
[Q1 Third Party Legal Name],
[Q8 Taxonomy Description],
[Q8.2 Commodity Codes Selected],
SubField([Q8.2 Commodity Codes Selected], ',') AS Q8.2_Commodity_Codes_Selected,
[Q11 Mkts 3Rd Party Perform Svc],
FROM
[$(VExternalDirectory)TPO IR Report.xlsx]
(ooxml, embedded labels, table is [TPO IR Report]);

LOAD [Commodity Codes] as Q8.2_Commodity_Codes_Selected,
Valid
FROM
[$(VExternalDirectory)Valid Commodity Codes.xlsx]
(ooxml, embedded labels, table is [Valid Codes]);

The Valid Column contains a "Yes"

[zebhashmi]

LOAD [Commodity Codes] as Q8.2_Commodity_Codes_Selected,
if(len(Vaild)=0,'No',Valid) as Valid1
FROM
[$(VExternalDirectory)Valid Commodity Codes.xlsx]
(ooxml, embedded labels, table is [Valid Codes]);

[brf10043]

Thanks Jahanzeb,  However, that doesn't exactly work.  I am able to use if(len(Vaild)=0,'No',Valid) as an expression to get the nulls in the table changed to a "No", but the code has no effect in the load script as the Valid Codes table only includes a list of valid Commodity Codes and a yes.  At that point that I am loading the Valid Codes table there are no nulls.  It's only when the list of Valid Codes is linked to the Data table to we end up with rows with nulls in the Valid? column.

[vishsaggi]

May be try this?

Data:
LOAD [Request Id],
[Q1 Third Party Legal Name],
[Q8 Taxonomy Description],
[Q8.2 Commodity Codes Selected],
SubField([Q8.2 Commodity Codes Selected], ',') AS Q8.2_Commodity_Codes_Selected,
[Q11 Mkts 3Rd Party Perform Svc],
FROM
[$(VExternalDirectory)TPO IR Report.xlsx]
(ooxml, embedded labels, table is [TPO IR Report]);

LEFT JOIN(Data)

LOAD [Commodity Codes] as Q8.2_Commodity_Codes_Selected,
Valid
FROM
[$(VExternalDirectory)Valid Commodity Codes.xlsx]
(ooxml, embedded labels, table is [Valid Codes]);

Noconcatenate

Final:

LOAD *, Alt(Valid, 'No') AS NewValidField

Resident Data;

Drop Table Data;

[zebhashmi]

Thank you!

Can you try that

if(Vaild-="yes",'No',Valid) as Valid1

[adria · Accepted solution]

I like ApplyMap's in these cases because you can supply an alternate value if the join would have left you with a null.

[Valid Codes Map]:

Mapping LOAD [Commodity Codes] as Q8.2_Commodity_Codes_Selected,
Valid
FROM
[$(VExternalDirectory)Valid Commodity Codes.xlsx]
(ooxml, embedded labels, table is [Valid Codes]);

Data:
LOAD [Request Id],
[Q1 Third Party Legal Name],
[Q8 Taxonomy Description],
[Q8.2 Commodity Codes Selected],
SubField([Q8.2 Commodity Codes Selected], ',') AS Q8.2_Commodity_Codes_Selected,

ApplyMap('Valid Codes Map',[Q8.2 Commodity Codes Selected],'No')    as Valid
[Q11 Mkts 3Rd Party Perform Svc],
FROM
[$(VExternalDirectory)TPO IR Report.xlsx]
(ooxml, embedded labels, table is [TPO IR Report]);