topic Re: Generic Load is not working on Date with Slash or hyphen or [Full Name] in QlikView

Generic Load is not working on Date with Slash or hyphen or [Full Name]

Vishal — Mon, 22 Aug 2022 17:41:38 GMT

Hello Everyone,

Thanks for the help in advance. I am stuck at Generic Load, wherein I need to convert Date as 15-04-16 to Column name, or a value with full name, such as 'Other Users'.

I have attached a file here, which contain three scenarios. Please have a look.

I have table as below, but dates are as 15-04-16 or in some cases - Other Uses.

And I want to transpose the above table as below.

For this, I have used below script -

Ind:
Load * Inline[
Date, Indication_flag, NPS
15Apr16, NPS, 2
26Apr16, NPS, 3
27Apr16, NPS, 4
29Apr16, NPS, 5
30Apr16, NPS, 6
];

NTranspose:
Generic LOAD
Indication_flag, Date as Date, NPS
Resident Ind;

NoConcatenate
Data:
Load Distinct Indication_flag Resident Ind;

For i=0 to NoOfTables()

TableList:
LOAD
TableName($(i)) as TableName
AutoGenerate 1
Where WildMatch(TableName($(i)), 'NTranspose*');

NEXT i

For j=1 to FieldValueCount('TableName')

LET vTable = FieldValue('TableName', $(j));
Left Join(Data) LOAD * Resident $(vTable);
Drop Table $(vTable);

Next j
Drop Tables Ind, TableList;

Now, wherever there is a date containing hyphen or slash, or full name as value, I get below error. Kindly help!

Thanks for the help!

Re: Generic Load is not working on Date with Slash or hyphen or [Full Name]

Taoufiq_Zarra — Mon, 22 Aug 2022 20:42:49 GMT

@Vishal

can you try this proposition :

Input: load * inline [ Date,Indicate_flag,NPS 15Apr16,NPS,2 26Apr16,NPS,3 27Apr16,NPS,4 29Apr16,NPS,5 30Apr16,NPS,6 ]; DATA: Generic LOAD Indicate_flag, Date, NPS Resident Input; output: Load distinct Indicate_flag resident Input; Drop Table Input; FOR i = NoOfTables()-1 to 0 STEP -1 LET vTable=TableName($(i)); //trace $(i) - '$(vTable)'; IF WildMatch('$(vTable)', 'DATA.*') THEN LEFT JOIN ([output]) LOAD distinct * RESIDENT [$(vTable)]; DROP TABLE [$(vTable)]; ENDIF NEXT i

input:

output:

Re: Generic Load is not working on Date with Slash or hyphen or [Full Name]

Vishal — Tue, 23 Aug 2022 09:04:27 GMT

Thanks @Taoufiq_Zarra ! it worked for me.