topic Re: Script merging in QlikView

Script merging

joshrussin — Mon, 14 Aug 2017 22:10:01 GMT

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?

Re: Script merging

MarcoWedel — Mon, 14 Aug 2017 22:24:52 GMT

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

regards

Marco

Re: Script merging

gibsontk1 — Mon, 14 Aug 2017 22:30:23 GMT

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!

Re: Script merging

joshrussin — Tue, 15 Aug 2017 02:25:56 GMT

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

Re: Script merging

devarasu07 — Tue, 15 Aug 2017 02:45:00 GMT

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

Re: Script merging

joshrussin — Tue, 15 Aug 2017 03:41:50 GMT

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.

Re: Script merging

sunny_talwar — Tue, 15 Aug 2017 18:07:31 GMT

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

Re: Script merging

joshrussin — Tue, 15 Aug 2017 18:43:18 GMT

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.

Re: Script merging

sonkumamon — Tue, 15 Aug 2017 20:30:59 GMT

Try preceding load:

LOAD

PO_Item_Batch_ECC & "Posting Date" as ECCPO

;

LOAD

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

FROM [*.qvd];