Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
sodavid
Contributor II
Contributor II

Load field with multiple columns

Dear Qlik Community,

I would like to pick "only" the content in field
FI,EP,EB,ES,EN and display as;

Prod Id, FI as Product, EP as Prefix, EB as Basis, ES as Suffix and EN as Partnumber (EP&EB&ES).
I have attached a sample excel for your reference.

Thanks in advance for your utmost response.

 

Samson O'David

Labels (1)
1 Solution

Accepted Solutions
ogster1974
Partner - Master II
Partner - Master II

OK you need to start looking at what's going on with your data and not just copying and pasting.  I can see in your code you're not understanding if you rename something you need to follow it through the rest of the code.

Rename table Products.FI to Maint; should really be Rename table Maint.FI to Maint; if that's what you've brought in.

Put an Exit Script; before the generic and load data and look at the data model.  Do you already have a table called Maint?  If you do you either need to rename that table or rename the Generic table.

If you then move your exit script to above the replace and reload do you get something like this?

ogster1974_0-1676113340928.png

You should then be renaming and joining on that newly named table.

View solution in original post

8 Replies
ogster1974
Partner - Master II
Partner - Master II

Try

Generic
Products:
Load * Inline [
Prod Id, Prod Nr Type, Prod Nr
afgsjoi48890088, FI, 123456
afgsjoi48890088, EP, abc
afgsjoi48890088, EB, 5005
afgsjoi48890088, ES, xy
afgsjoi48890088, EN, abc5005xy
];

Rename table Products.FI to Products;

Outer Join(Products)
Load
*
Resident Products.EP;

Outer Join(Products)
Load
*
Resident Products.EB;

Outer Join(Products)
Load
*
Resident Products.ES;

Outer Join(Products)
Load
*
Resident Products.EN;

Drop Tables Products.EB,Products.EN,Products.EP, Products.ES;

Rename Field FI to Product;
Rename Field EP to Prefix;
Rename Field EB to Basis;
Rename Field ES to Suffix;
Rename Field EN to Partnumber;

ogster1974_0-1676051953294.png

 

sodavid
Contributor II
Contributor II
Author

Thanks for your swift response. I am getting the following on data load process

The following error occurred:
The name 'Products' already exists.
 
The error occurred here:
Rename table Products.FI to Products
ogster1974
Partner - Master II
Partner - Master II

Then that says youve alread got a table named Products in your model already.

 

Please note you should replace the inline load with my example data with your data if you haven't already.

sodavid
Contributor II
Contributor II
Author

Hi,

I loaded the script as follows considering that in my source file I have over 100k records

Maint:
LOAD
"Prod Id",
"Prod Nr Type",
"Product Nr"
FROM [lib://......txt]
(txt, utf8, embedded labels, delimiter is '|', msq)

Where Match ("Prod Nr Type", 'FI','EP', 'EB', 'ES', 'EN');

ogster1974
Partner - Master II
Partner - Master II

Have you included the keyword Generic above Maint:

then replaced 

Products with Maint in your script?

 

sodavid
Contributor II
Contributor II
Author

Yes I have with the following message

The following error occurred:
The name 'Maint' already exists.
 
The error occurred here:
Rename table Products.FI to Maint
ogster1974
Partner - Master II
Partner - Master II

OK you need to start looking at what's going on with your data and not just copying and pasting.  I can see in your code you're not understanding if you rename something you need to follow it through the rest of the code.

Rename table Products.FI to Maint; should really be Rename table Maint.FI to Maint; if that's what you've brought in.

Put an Exit Script; before the generic and load data and look at the data model.  Do you already have a table called Maint?  If you do you either need to rename that table or rename the Generic table.

If you then move your exit script to above the replace and reload do you get something like this?

ogster1974_0-1676113340928.png

You should then be renaming and joining on that newly named table.

sodavid
Contributor II
Contributor II
Author

Thanks for your feedback, I believe its more than just copy paste. I have been able to load the file with Generic Maint: as my main table  and afterwards tried the load in line function. I have achieved the result that I was expecting. Your support is very much appreciated.