topic Load Script Question in QlikView

Load Script Question

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

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?

Re: Load Script Question

brf10043 — Thu, 01 Feb 2018 18:18:18 GMT

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"

Re: Load Script Question

zebhashmi — Thu, 01 Feb 2018 18:25:34 GMT

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

Re: Load Script Question

brf10043 — Thu, 01 Feb 2018 19:22:49 GMT

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.

Re: Load Script Question

vishsaggi — Thu, 01 Feb 2018 19:42:05 GMT

May be try this?

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;

Re: Load Script Question

zebhashmi — Thu, 01 Feb 2018 20:12:04 GMT

Thank you!

Can you try that

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

Re: Load Script Question

adria — Thu, 01 Feb 2018 22:42:39 GMT

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