Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all.
I created a loop to proceed some table data. The part where I transpose the table doesn't work out as I expect.
For TableID #1 column names applied as expected.
For TableID #2 column names appeared as same sa for TableID #1.
I will give an example of what I have and what I want to get. And I will be appreciate for any hint to solve this issue.
Table #1 before transpose |
Internal_ID | Question_Name_Full | Value |
ID1 | Name | Una |
ID2 | Name | Lena |
ID1 | Full Name | Roberts |
ID2 | Full Name | Smith |
ID1 | Age | 22 |
ID2 | Age | 33 |
Table #2 before transpose |
Internal_ID | Question_Name_Full | Value |
ID44 | Login | UnaR |
ID55 | Login | LenaS |
ID44 | Pass | 123 |
ID55 | Pass | 456 |
Table #1 after transpose |
Name | Full Name | Age |
Una | Roberts | 22 |
Lena | Smith | 33 |
Table #2 after transpose EXPECTED |
Login | Pass |
UnaR | 123 |
LenaS | 456 |
Table #2 after transpose ACTUAL |
Name | Full Name |
UnaR | 123 |
LenaS | 456 |
For some reason for second table I get result with headers from the first table. I have no idea why does this happened.
Here is the script below.
Set Table_ID_List = '1', '2';
For Each ID in $(Table_ID_List )
Mapping_Question_Name:
Mapping Load
QID,
Name
From Question.qvd(qvd);
Source:
Load Distinct
Internal_ID,
ApplyMap('Question_Name', QuestionID, Null()) as Question_Name_Full,
Value
From Source_$(ID).qvd(qvd);
Source_Entities:
NoConcatenate Load Distinct
Internal_ID
Resident Source;
For i = 1 to FieldValueCount('Question_Name_Full')
Let v_Field = FieldValue('Question_Name_Full', $(i));
Left Join (Source_Entities)
Load Distinct
Internal_ID,
Value as [$(v_Field)]
Resident Source
Where Question_Name_Full= '$(v_Field)' ;
Next i
Drop Table Source;
Drop Fields Internal_ID from Source_Entities;
Store Source_Entitiesin to Source_Entities_$(ID).qvd];
Drop Table Source_Entities;
Next ID;
Hi Peony,
it seems like your problem might be your map. Since you are giving your field names through your variable
[$(v_Field)]
which is getting it values from field
Question_Name_Full
instead of trying to fix this issue i would recomment to approach this problem different. Check this link for the generic load and try it that way. It seems like this is what you are trying to achieve on a workaround.
https://community.qlik.com/t5/Qlik-Design-Blog/The-Generic-Load/ba-p/1473470
Let me know if it helped.
Regards,
can
Hi @canerkan Thank you for your advise. Will try this option.
Hi Peony,
it seems like your problem might be your map. Since you are giving your field names through your variable
[$(v_Field)]
which is getting it values from field
Question_Name_Full
instead of trying to fix this issue i would recomment to approach this problem different. Check this link for the generic load and try it that way. It seems like this is what you are trying to achieve on a workaround.
https://community.qlik.com/t5/Qlik-Design-Blog/The-Generic-Load/ba-p/1473470
Let me know if it helped.
Regards,
can
Hi @canerkan Thank you for your advise. Will try this option.