Script merging

[joshrussin]

I need to create a new column that merges 3 different columns with a space between.

This is what I have:

"Purchase Order" + " " + Material + " " + Batch as PO_Item_Batch_ECC

It tells me that " " is not a valid field.

If I take it out, it merges all without a space, but I need a space. Any help?

[MarcoWedel]

"Purchase Order" & ' ' & Material & ' ' & Batch as PO_Item_Batch_ECC

regards

Marco

[gibsontk1]

Try using & instead of + to concatenate strings and single quotes for the spaces, so that it's:

"Purchase Order" & ' ' & Material & ' ' & Batch as PO_Item_Batch_ECC

The ampersand & is used for string concatenation in the load script, instead of the usual + like most programming languages. Also, double quotes denote field names, while single quotes denote string literals.

Hope this helps!

[joshrussin]

That works perfectly!

Is there any way to create a column from a created column?

This is what I was trying.

"Purchase Order" & ' ' & Material & ' ' & Batch as PO_Item_Batch_ECC,

PO_Item_Batch_ECC & "Posting Date" as ECCPO

[devarasu07]

Hi,

You can try like below with Preceding load,

Load *,

PO_Item_Batch_ECC & '| ' & "Posting Date" as ECCPO;

Load *,

"Purchase Order" & '| ' & Material & '| ' & Batch as PO_Item_Batch_ECC

Resident Fact;

PS: instead of two merge column, u can simply in one expression.

"Purchase Order" & '| ' & Material & '| ' & Batch &'|'& "Posting Date"  as ECCPO

Thanks,Deva

[joshrussin]

I am just trying to recreate this table in QlikView from the raw data of the excel.

In the excel, the white columns are created via VLOOKUPS and defining names.

Could you assist me, attached is what I have so far.

[sunny_talwar]

May be this

MappingTable:

Mapping

LOAD

    "PO Material Batch",

    "Posting Date"

FROM [lib://Lib/11 August 2017 (002).XLSX]

(ooxml, embedded labels, table is ECC);

Fact:

LOAD *,

"Putaway Date (Act.)" - [ECC Posting Date] as [Lead Time];

LOAD Date(ApplyMap('MappingTable', "Purchase Order" & ' ' & Product & ' ' & Batch, 'N/A')) as [ECC Posting Date],

"Purchase Order" & ' ' & Product & ' ' & Batch as [PO Product Batch],

    "Purchase Order",

    Product,

    Document,

    Batch,

    "Party Entitled to Dispose",

    "Document Category",

    "Planned Time of Goods Receipt",

    "Actual Goods Receipt Date",

    "Actual Time of Goods Receipt",

    "Planned Time of Unloading",

    "Actual Unloading Date",

    "Actual Time of Unloading",

    "Planned Time of Putaway",

    "Putaway Date (Act.)",

    "Actual Time of Putaway",

    "ERP Document",

    "ERP Document Item"

FROM [lib://Lib/11 August 2017 (002).XLSX]

(ooxml, embedded labels, table is eWM);

[joshrussin]

It worked in the version you sent me but when I added it to my actual sheet, it says PO Material Batch can't be found. It is not an already created column in the excel sheet. I am trying to get qlik to create it. Here is my app now.

[sonkumamon]

Try preceding load:

LOAD

PO_Item_Batch_ECC & "Posting Date" as ECCPO

;

LOAD

"Purchase Order" & ' ' & Material & ' ' & Batch as PO_Item_Batch_ECC

     FROM [*.qvd];