topic Re: Resident Load Not Working With CrossTable in QlikView

Resident Load Not Working With CrossTable

lucasdavis500 — Wed, 25 Nov 2020 16:16:04 GMT

I'm having issues trying to create a new table based off of a crosstable that I'm creating from raw data.

Basically, I have raw data, not in the format I need to use for qlikview logic. I am creating a cross table out of it like so

CrossTable(FurnType, Furnishings, 1)
LOAD Date AS [Date Created],
Furn_Auto,
Furn_AutoOther,
Furn_Helon,
Furn_Heloc,
Furn_FinC,
Furn_Mtg
FROM
$(vPathname)furn data.txt
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Afterwards, I'm trying to concatenate this to my actual table that's within the same tab.

CONCATENATE

LOAD *
RESIDENT [furn data];

However. This is not working. I tried a thousand variations of this. When I use the script debugger, I can see Qlikview is maintaining the exact same table when I do a resident load.

The funny part is, If I even change this code to be like this:

OldTable:

NewTable:
LOAD *
RESIDENT OldTable;

NewTable isn't even created. I can literally see it in the debugger

It says NewTable << Data_Model 1 lines fetched

Why is it not fetching lines from OldTable??? I literally have RESIDENT OldTable directly under it. Whenever the script is done the only thing that remains is OldTable. If I DROP OldTable; and name/dont name my new table that is a resident load, it doesnt matter, no new table is created. It doesn't matter if i explicity state the variables instead of *, QV recognizes code for NewTable in the debugger and just doesn't seem to care afterward.

I don't understand what I'm doing wrong. All I want to do is create this cross table, and then concatenate the data after all of my fields are aggregated into one. This is driving me insane trying to figure out why it's not working, and looking at other people's code it seems they do the exact same thing I'm doing but it's working for them. Any help would be greatly appreciated

Re: Resident Load Not Working With CrossTable

MK_QSL — Fri, 20 Jan 2017 18:14:35 GMT

What exactly you are trying to do?

Load a table..

Convert to crosstable...

Now concatenate both tables..

is that?

Re: Resident Load Not Working With CrossTable

trdandamudi — Fri, 20 Jan 2017 18:17:11 GMT

See if the below code works, I added 'Noconcatenate' : (You will get synthetic keys.. but still you should see two tables like OldTable and NewTable)

OldTable:

NoConcatenate:

NewTable:

LOAD *
RESIDENT OldTable;

Re: Resident Load Not Working With CrossTable

lucasdavis500 — Fri, 20 Jan 2017 18:49:13 GMT

I should have been more clear, sorry. I have many more tables above this code that are being concatenated

load *

from myqvd;

concatenate

load *

from myqvd2;

Afterwards, I'm trying to concatenate this to my actual table that's within the same tab.

CONCATENATE

LOAD *
RESIDENT [furn data];

now i want this table

CONCATENATE

LOAD *
RESIDENT [furn data];

concatenated to tables that were created from myqvd1 and 2 , which isn't seeming to work.

Re: Resident Load Not Working With CrossTable

lucasdavis500 — Fri, 20 Jan 2017 18:49:31 GMT

Hi Thirumala, thanks for your reply, Please see comments above

Re: Resident Load Not Working With CrossTable

MK_QSL — Fri, 20 Jan 2017 18:53:12 GMT

I can't see fun data table anywhere..

Re: Resident Load Not Working With CrossTable

swuehl — Fri, 20 Jan 2017 18:55:55 GMT

Use

CONCATENATE (TableName)

LOAD *
RESIDENT [furn data];

where TableName is the name of the table you want to append the data to.

Re: Resident Load Not Working With CrossTable

lucasdavis500 — Fri, 20 Jan 2017 18:57:28 GMT

I fixed the issue doing something I would hoped that I didn't need to do.. storing tmp data into qvd and reading it out again, then concatenating. i.e.

OldTable:

store into $(mypath)myqvd(qvd);

drop table OldTable;

Concatenate

LOAD

*
from $(mypath)myqvd(qvd);

Re: Resident Load Not Working With CrossTable

lucasdavis500 — Fri, 20 Jan 2017 18:58:09 GMT

furn data is the implicit table name when not stated since in the file I am loading from is furn data.txt

Re: Resident Load Not Working With CrossTable

MK_QSL — Fri, 20 Jan 2017 19:00:52 GMT

T1;

Load * From YourTable;

T2;

CrossTable(....)

Load .... Resident Table;

Concatenate(T2)

Load * Resident whatevertable;

Re: Resident Load Not Working With CrossTable

swuehl — Sat, 21 Jan 2017 12:55:12 GMT

I don't think you need the QVD store.

If you do something like

CONCATENATE

LOAD * RESIDENT OldTable;

then QV will concatenate the records to the table you created in the previous LOAD statement of your script, that is OldTable. You can see this in the log, OldTable retrieves records twice, doubling the number of records to 144.

I think you want to append the lines to a different table instead, then you need to state the table name like

CONCATENATE (Data_Model)

LOAD * RESIDENT OldTable;

or maybe

CONCATENATE (furn_data)

LOAD * RESIDENT OldTable;

not sure where you want to append your records to.

And the reason why

NewTable:
LOAD *
RESIDENT OldTable;

hasn't created a table called NewTable is that QlikView will auto-concatenate tables with same name and number of fields. Hence the records loaded from OldTable have been added to your resident table OldTable.

edit: And don't forget to drop all tables that are not needed in the model at the end of your script, like assumingly OldTable, to avoid synthetic keys.