Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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);
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
What exactly you are trying to do?
Load a table..
Convert to crosstable...
Now concatenate both tables..
is that?
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:
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);
NoConcatenate:
NewTable:
LOAD *
RESIDENT OldTable;
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;
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];
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.
Hi Thirumala, thanks for your reply, Please see comments above
I can't see fun data table anywhere..
Use
CONCATENATE (TableName)
LOAD *
RESIDENT [furn data];
where TableName is the name of the table you want to append the data to.
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:
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);
store into $(mypath)myqvd(qvd);
drop table OldTable;
Concatenate
LOAD
*
from $(mypath)myqvd(qvd);
furn data is the implicit table name when not stated since in the file I am loading from is furn data.txt
T1;
Load * From YourTable;
T2;
CrossTable(....)
Load .... Resident Table;
Concatenate(T2)
Load * Resident whatevertable;