Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dilip1234
Creator
Creator

How to split one qlik table into multiple tables based on conditions?

Hi,

I have one table called Documents. In Documents table, I have one column "DocumentLevelKey" which will have numeric value 1 to 3.

Please find the meaning of this numeric value:

Numeric ValueDetails
1Geo level document.
2City level document.
3Country level document.

I want to split "Documents" table based on DocumentLevelKey, I want to have following tables:

  1. GeoDocucments
  2. CityDocuments
  3. CountryDocuments

Script of Document table:


//Qlik internal table

LOAD

        [Id] AS [CMM_Document.Id],

        [DocumentLevelKey],

        [LevelId] AS [GeographyCode],

        [DocumentDesc] AS DocumentDescription,

        [Status] AS [CMM_Document.Status],

        [Delete Ind] AS [CMM_Document.DeleteInd],

        CreatedBy AS [CMM_Document.CreatedBy],

        UpdatedBy AS [CMM_Document.UpdatedBy],

        Date([Created Date], '$(TimestampFormat)') AS [CMM_Document.CreatedDate],

        Date([UpdatedDate],'$(TimestampFormat)') AS [CMM_Document.UpdatedDate];

   

//SQL table

[CMM_Document]:

SELECT

Id,

DocumentLevelKey,

LevelId,

[DocumentDesc],

[Status],

[Delete Ind],

CreatedBy,

[Created Date],

UpdatedBy,

UpdatedDate

FROM CMM_Document

WHERE [Status] = 'A' AND [Delete Ind] = 'N';


STORE CMM_Document INTO '$(vQVDPath)CMMDocument_QVD.Qvd' (qvd);

DROP TABLE CMM_Document;


[CMMDocument_QVD]:

LOAD * FROM

[$(vQVDPath)CMMDocument_QVD.Qvd](qvd);

[CMMDocument_Geo_QVD]:

LOAD * RESIDENT CMMDocument_QVD WHERE DocumentLevelKey=1;

[CMMDocument_City_QVD]:

LOAD * RESIDENT CMMDocument_QVD WHERE DocumentLevelKey=2;

[CMMDocument_Country_QVD]:

LOAD * RESIDENT CMMDocument_QVD WHERE DocumentLevelKey=3;

As you can see in above query, I have DocumentLevelKey column, I want to split table using where clause. I want CMMDocument_Geo_QVD, CMMDocument_City_QVD and CMMDocument_Country_QVD tables to be generated.

Can anyone suggest how can I achieve this?

Thanks

Dilip Solanki

Message was edited by: DILIP SOLANKI

Thanks
Dilip Solanki
4 Replies
jwjackso
Specialist III
Specialist III

Add a prefix to each of the fields.  For the geo data, Load id as g_id, documentkeylevel as g_documentkeylevel,...

dilip1234
Creator
Creator
Author

Hi Jerry,

Thanks for your suggestions. I apologize, I forgot to add some important details in my concern, could you please check now and suggest me.

Thanks

Dilip Solanki

Thanks
Dilip Solanki
Anonymous
Not applicable

As you are doing 3 Load * from the same resident tables all 3 will by definition have identical columns so I suspect they will all auto concatenate into a single table.  To avoid this you will have to use NoConcatenate as in :

NoConcatenate

[CMMDocument_Geo_QVD]:

LOAD * RESIDENT CMMDocument_QVD WHERE DocumentLevelKey=1;

NoConcatenate

[CMMDocument_City_QVD]:

LOAD * RESIDENT CMMDocument_QVD WHERE DocumentLevelKey=2;

NoConcatenate

[CMMDocument_Country_QVD]:

LOAD * RESIDENT CMMDocument_QVD WHERE DocumentLevelKey=3;

I don't know what you wish to do afterwards with these tables, but you'll need to something to avoid a horrible synthetic key.

jwjackso
Specialist III
Specialist III

As Bill Markham said, you can use NoConcatenate and have really big synthetic key.  To avoid the synthetic key, each table needs different field names.  It doesn't matter if you load from original query or resident table.

We still don't know the actual need for 3 different tables.  If you want to limit the data in a chart, in an expression for the count of CityLevel documents, you could Count ({<DocumentKeyLevel={'2'}>} [CMM_Document.Id])