Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

How to combine my 3 box into 1 box from Table View ?

Hi All

Below script work fine :-

SET vDevelopment = 0; //server = 0 // =2 for PY NEW NB // 1 FOR PY OLD NB
IF $(vDevelopment) = 0 THEN
SET vRAWPath = 'C:\Users\pauly\Dropbox\QV_RAW_ISDN\'; //server folder
SET vFile200 = 'GL_PM.CSV';
SET vFile261 = 'FS_TDS_ADL.TXT';
else
END IF

For Each i in '$(vFile200)'
LOAD
'$(i)' as SOURCE_,
[GL Code],
[Customer/Vendor Name]
FROM
$(vRAWPath)$(i)
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
NEXT i;

For Each i in '$(vFile261)'
LOAD
'$(i)' as SOURCE_,
ApplyMap('MAP_TDS_OTH_INCOME',[@1:16T],'OTH_INCOME') as [oTH_INCOME],
if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount]
FROM
$(vRAWPath)$(i)
(ansi, fix, no labels, header is 0, record is line);
NEXT i;

Above script generate below table model :-

DATA JOIN.png
May i know how to combine all 3 table into one table ?

Paul

1 Solution

Accepted Solutions
Or
Master II
Master II

CONCATENATE, not COCATENATE.

View solution in original post

9 Replies
Or
Master II
Master II

It is not possible for the script in your post to have generated this data model - the fields completely fail to match your Load statements. Clearly there's more script that you didn't include.

If you want to force multiple tables to combine, use the CONCATENATE statement. If they're supposed to be joined based on the key fields, use JOIN (or LEFT JOIN).

 

 

paulyeo11
Master
Master
Author

Hi Sir

Yes i agree with you need to use CONCATENATE statement  , but the issue is when i add the CONCATENATE , i get error. See the post below :-

https://community.qlik.com/t5/QlikView-Scripting/Why-i-can-not-add-Concatenate-in-between-FOR/td-p/1...

May be you can advise me where go wrong ?

Paul

Or
Master II
Master II

First, load your full tables (without joining or concatenating).

Then, concatenate the RESIDENT second table to the first table.

Then, drop the original second table.

Table1:

Load (Whatever) FROM {Place};

Table2:

Load (OtherWhatever) FROM {OtherPlace};

CONCATENATE(Table1)

LOAD * Resident Table2;

Drop Table Table2;

paulyeo11
Master
Master
Author

Hi Sir

I follow your advise , i get error below :-

I get error :-

Unknown statement
COCATENATE(GL_PM)

LOAD * Resident FS_TDS_ADL

I try to  :-

COCATENATE(GL_PM): 

COCATENATE(GL_PM);

Still Cannot. 

i notice that the script have error , see the below image , as Load should be blue color :-

black color syntax.png

Meaning After next i; cannot add COCATENATE command.

Paul Yeo

Or
Master II
Master II

CONCATENATE, not COCATENATE.

View solution in original post

paulyeo11
Master
Master
Author

Hi Sir

Thank you very much , it work fine now.

Paul Yeo

paulyeo11
Master
Master
Author

Hi Sir

After adding script on partial reload , it work fine . May i know how to add some more code , so that it can help to remove the syn-key ? So that it can make my table look neat.

I add Partial reload script :-

CODING.png

My Table look :-

PARTIAL TABLE.png

Paul Yeo

Or
Master II
Master II

Again, all these extra fields aren't coming from the load script you posted, so I'm not able to guess where they are coming from and how you would go about appending them to the other table. Clearly you have more than one load here and they don't have the same columns..

paulyeo11
Master
Master
Author

Hi Sir

After I remove one field from below :-

load SOURCE_,GL_CODE

Using this at my partial reload script :-

GL_TABLE:
load SOURCE_,
 if (SOURCE_='GL_PM.CSV','PM',
 if (SOURCE_='GL_LE.CSV','LE',
 if (SOURCE_='GL_PW.CSV','PW',
 if (SOURCE_='FS_TDS_ADL.TXT','TA',
 )))) as [SOURCE]
         
resident GL_TABLE;

Now the $syn key remove 

Thank 

Paul