Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
joshrussin
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
MarcoWedel

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


regards


Marco

gibsontk1
Partner - Contributor II
Partner - Contributor II

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
Creator III
Creator III
Author

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

devarasu07
Master II
Master II

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator
Creator

Try preceding load:

LOAD

PO_Item_Batch_ECC & "Posting Date" as ECCPO

;

LOAD

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

     FROM [*.qvd];