topic Re: Issues Combining Tables Following Generic Load in App Development

Issues Combining Tables Following Generic Load

Benomi — Sat, 16 Nov 2024 17:24:57 GMT

Hello All,

I am having some issues dressing up some data for analysis in Qlik Sense. The raw data that I'm working with contains category under a single field in a string formatted like: id,1234,name,CategoryName,description,descriptioninfo....etc.

Using Subfield() I have a table of category elements formatted as follows:

ItemName, Key, Value

item1, id, 1234

item1, name, category1

item1, description, info1

item2, id, 2341

item2, name, category2

item2, description, info2

etc...

I understand that a generic load will help me split the elements so I can join the tables together and drop the extras, however my script doesn't seem to accomplish this. On reloading my data, all the generic tables remain, and none of them are joined to the final table. My script is as follows:

Category:
Generic
Load name as name,
key as key,
value as value
Resident CategoryTableElements;

Set vListOfTables = ;

For vTableNo = 0 to NoOfTables()-1
Let vTableName = TableName($(vTableNo));
If SubField(vTableName,'.',1)='Category' THEN
Let vListOfTables = vListOfTables
& if(Len(vListOfTables) > 0, ',')
& Chr(39) & vTableName & Chr(39);
End If
Next vTableNo

CombinedTable:
Load Distinct name,
key,
value
Resident CategoryTableElements;

For Each vTableName In $(ListOfTables)
Left Join(CombinedTable) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName

Despite following examples online, and the module in the Continuous Classroom verbatim, my tables don't get combined. Any advice is greatly appreciated!

Re: Issues Combining Tables Following Generic Load

Saravanan_Desingh — Sat, 06 Mar 2021 01:47:54 GMT

Are you looking something like this?

CategoryTableElements: LOAD * INLINE [ ItemName, Key, Value item1, id, 1234 item1, name, category1 item1, description, info1 item2, id, 2341 item2, name, category2 item2, description, info2 ]; Category: Generic Load ItemName, Key, Value Resident CategoryTableElements; Drop Table CategoryTableElements;

Re: Issues Combining Tables Following Generic Load

Saravanan_Desingh — Sat, 06 Mar 2021 01:48:23 GMT

Output:

Re: Issues Combining Tables Following Generic Load

rwunderlich — Sun, 07 Mar 2021 18:31:09 GMT

I believe your test for tablename "Category.*" is incorrect. You should be testing a string (with single quotes).

If SubField('$(vTableName)' ,'.',1)='Category' THEN

I think an overall simpler script to combine might be:

CombinedTable:
Load Distinct name,
    key,
    value
Resident CategoryTableElements;

FOR i = NoOfTables()-1 to 0 STEP -1 
  LET vTable=TableName($(i)); 
  IF WildMatch('$(vTable)', 'Category.*') THEN 
    LEFT JOIN (CombinedTable) LOAD * RESIDENT    [$(vTable)]; 
    DROP TABLE  [$(vTable)]; 
  ENDIF 
NEXT i

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Issues Combining Tables Following Generic Load

Benomi — Mon, 08 Mar 2021 18:23:55 GMT

That'll do it! Thanks a ton for the help. Your solution is much more concise than the one provided in the QCC.