Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Script merging

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?

8 Replies
Highlighted

Re: Script merging

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


regards


Marco

Highlighted
Partner
Partner

Re: Script merging

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!

Highlighted
Creator III
Creator III

Re: Script merging

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

help.jpg

Highlighted
Master II
Master II

Re: Script merging

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

Highlighted
Creator III
Creator III

Re: Script merging

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.

Highlighted

Re: Script merging

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

Highlighted
Creator III
Creator III

Re: Script merging

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.

Highlighted
Contributor III
Contributor III

Re: Script merging

Try preceding load:

LOAD

PO_Item_Batch_ECC & "Posting Date" as ECCPO

;

LOAD

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

     FROM [*.qvd];