Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 Value | Details |
---|---|
1 | Geo level document. |
2 | City level document. |
3 | Country level document. |
I want to split "Documents" table based on DocumentLevelKey, I want to have following tables:
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
Add a prefix to each of the fields. For the geo data, Load id as g_id, documentkeylevel as g_documentkeylevel,...
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
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.
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])