Transformation of Data

Nikhil2725 · ‎2019-02-05

Hi All,

I have data listed below for the reference.

Lender_id	Question_text	Answer_text
1	How are you	Fine!
1	How old are you	21
1	What is your name	ABC
2	Which is your fav color	Red
2	Which is your Sport	Cricket
2	Which is fav dish	Non-Veg

Further, I want to display as below: Kindly help me in doing this..

Lender_id	Q1	A1	Q2	A2	Q3	A3
1	How are you	Fine!	How old are you	21	What is your name	ABC
2	Which is your fav color	Red	Which is your Sport	Cricket	Which is fav dish	Non-Veg

Nikhil2725 · ‎2019-02-06

Hello,

Im using the Database query here. However Im getting an error as QuestionNum is not found. can u help in this regards.
In Questions Table we don have any field called QuestionNum

Table:
LOAD lender_offer_id,question_text,answer_text,
If(lender_offer_id= Previous(lender_offer_id),
RangeSum(Peek('QuestionNum'), 1), 1) as QuestionNum,
;

LOAD
question_text, answer_text,question_id,answer_id,lender_offer_id
Resident Questions;

FinalTable:
LOAD Distinct lender_offer_id
Resident Table;

FOR i = 1 to FieldValueCount('QuestionNum')

LET vField = FieldValue('QuestionNum', $(i));

Left Join (FinalTable)
LOAD lender_offer_id,
question_text as [Q$(vField)],
answer_text as [A$(vField)]
Resident Table
Where QuestionNum = $(vField);

NEXT i
DROP Table Table;

Nikhil2725 · ‎2019-02-07

Hello Mohammed Mukram,
While I'm trying load the data to my project I'm getting an error like "Field 'QuestionNum' not found"
Can u please help me in this??

mdmukramali · ‎2019-02-07

Hi,

QuestionNum we are creating for just to hold the number of records in the tables.

Table:
LOAD lender_offer_id,question_text,answer_text,
If(lender_offer_id= Previous(lender_offer_id),
RangeSum(Peek('RecordNumber'), 1), 1) as RecordNumber
;

LOAD
question_text, answer_text,question_id,answer_id,lender_offer_id
Resident Questions;

FinalTable:
LOAD Distinct lender_offer_id
Resident Table;

FOR i = 1 to FieldValueCount('RecordNumber')

LET vField = FieldValue('RecordNumber', $(i));

Left Join (FinalTable)
LOAD lender_offer_id,
question_text as [Q$(vField)],
answer_text as [A$(vField)]
Resident Table
Where RecordNumber= $(vField);

NEXT i
DROP Table Table;

// can you try above script

I have seen in your script you loaded the table by using Resident table Question, have you dropped that table after resident load?

paste complete script here.

Nikhil2725 · ‎2019-02-07

Hi Mohammed Mukram,
Kindly check the code......
Lender:
LOAD `lender_offer_id`,
subjects;
SQL SELECT `lender_offer_id`,
subjects
FROM rcdb.lender_offer;

left Join(Lender)
LOAD `certificate_access_log_id`,
`lender_offer_id`,
`certificate_download_count`,
`certificate_email_count`;
SQL SELECT `certificate_access_log_id`,
`lender_offer_id`,
`certificate_download_count`,
`certificate_email_count`
FROM rcdb.certificateaccesslog;

Temp_Final_Status:
Mapping LOAD `lender_offer_id`,
if(processed='1','True') as rewardStatus;
SQL SELECT `lender_offer_id`,
processed
FROM rcdb.rewardstatus;

LOAD `course_completion_id`,
`invitation_id`,
final_test_appeared_date,percentage_score,
`lender_offer_id`,
`final_test_status`,
`course_completed`,
`date_completed`,
APPLYMAP('Temp_Final_Status',lender_offer_id, 'False') as mapped_rewardStatus,
`survey_completed`,test_attempt_count;
SQL SELECT `course_completion_id`,
`invitation_id`,
`lender_offer_id`,
`final_test_status`,
`course_completed`,
`date_completed`,test_attempt_count,
`survey_completed`,final_test_appeared_date,percentage_score
FROM rcdb.coursecompletion;

tempTable:

LOAD `lender_offer_id`,
score,
`percentage_score`,
`test_type`,
`date_created`;
SQL SELECT `lender_offer_id`,
score,
`percentage_score`,
`test_type`,
`date_created`
FROM rcdb.testresults;

mainTable:
Load
lender_offer_id,
Subfield(scoreData,';',1) as first_attempt,
Subfield(scoreData,';',2) as second_attempt,
Subfield(scoreData,';',3) as third_attempt;

Load
lender_offer_id,
Concat(score,';',date_created) as scoreData //third parameter of concat will sort the values based on i/p
Resident tempTable Group by lender_offer_id ;
Drop table tempTable;

Table:
LOAD lender_offer_id,
If(Len(Trim(SubField(first_attempt, '/', 1))) > 0, SubField(first_attempt, '/', 1)) as first_attempt_correct,
SubField(first_attempt, '/', 2) - SubField(first_attempt, '/', 1) as first_attempt_incorrect,
If(Len(Trim(SubField(second_attempt, '/', 1))) > 0, SubField(second_attempt, '/', 1)) as second_attempt_correct,
SubField(second_attempt, '/', 2) - SubField(second_attempt, '/', 1) as second_attempt_incorrect,
If(Len(Trim(SubField(third_attempt, '/', 1))) > 0, SubField(third_attempt, '/', 1)) as third_attempt_correct,
SubField(third_attempt, '/', 2) - SubField(third_attempt, '/', 1) as third_attempt_incorrect
Resident mainTable;

Questions:
LOAD
`question_id`,
`lender_offer_id`,answered_choice_id as answer_id;
SQL SELECT
`question_id`,
`lender_offer_id`,answered_choice_id
FROM rcdb.userquestion;

left join(Questions)
LOAD `question_id`,
`subject_id`;
SQL SELECT `question_id`,
`subject_id`
FROM rcdb.questions;

left join(Questions)
LOAD `question_text_id`,
`question_id`,
`language_id`,
`question_text`;
SQL SELECT `question_text_id`,
`question_id`,
`language_id`,
`question_text`
FROM rcdb.questiontext where language_id=1;

left join(Questions)
LOAD `answer_id`,
`question_id`;
SQL SELECT `answer_id`,
`question_id`
FROM rcdb.answers;

left join(Questions)
LOAD `answer_id`,
`answer_text`;
SQL SELECT `answer_id`,
`answer_text`
FROM rcdb.answertext where language_id=1;

Table:
LOAD lender_offer_id,question_text,answer_text,
If(lender_offer_id= Previous(lender_offer_id),
RangeSum(Peek('RecordNumber'), 1), 1) as RecordNumber
;

LOAD
question_text, answer_text,question_id,answer_id,lender_offer_id
Resident Questions;
FinalTable:
LOAD Distinct lender_offer_id
Resident Table;

FOR i = 1 to FieldValueCount('RecordNumber')

LET vField = FieldValue('RecordNumber', $(i));

Left Join (FinalTable)
LOAD lender_offer_id,
question_text as [Q$(vField)],
answer_text as [A$(vField)]
Resident Table
Where RecordNumber= $(vField);

NEXT i
DROP Table Table;

mdmukramali · ‎2019-02-07

Hi,

You have already one table with Same Table Name

change the table name to Table_TEMP

Lender:
LOAD `lender_offer_id`,
subjects;
SQL SELECT `lender_offer_id`,
subjects
FROM rcdb.lender_offer;

left Join(Lender)
LOAD `certificate_access_log_id`,
`lender_offer_id`,
`certificate_download_count`,
`certificate_email_count`;
SQL SELECT `certificate_access_log_id`,
`lender_offer_id`,
`certificate_download_count`,
`certificate_email_count`
FROM rcdb.certificateaccesslog;

Temp_Final_Status:
Mapping LOAD `lender_offer_id`,
if(processed='1','True') as rewardStatus;
SQL SELECT `lender_offer_id`,
processed
FROM rcdb.rewardstatus;

LOAD `course_completion_id`,
`invitation_id`,
final_test_appeared_date,percentage_score,
`lender_offer_id`,
`final_test_status`,
`course_completed`,
`date_completed`,
APPLYMAP('Temp_Final_Status',lender_offer_id, 'False') as mapped_rewardStatus,
`survey_completed`,test_attempt_count;
SQL SELECT `course_completion_id`,
`invitation_id`,
`lender_offer_id`,
`final_test_status`,
`course_completed`,
`date_completed`,test_attempt_count,
`survey_completed`,final_test_appeared_date,percentage_score
FROM rcdb.coursecompletion;

tempTable:

LOAD `lender_offer_id`,
score,
`percentage_score`,
`test_type`,
`date_created`;
SQL SELECT `lender_offer_id`,
score,
`percentage_score`,
`test_type`,
`date_created`
FROM rcdb.testresults;

mainTable:
Load
lender_offer_id,
Subfield(scoreData,';',1) as first_attempt,
Subfield(scoreData,';',2) as second_attempt,
Subfield(scoreData,';',3) as third_attempt;

Load
lender_offer_id,
Concat(score,';',date_created) as scoreData //third parameter of concat will sort the values based on i/p
Resident tempTable Group by lender_offer_id ;
Drop table tempTable;

Table:
LOAD lender_offer_id,
If(Len(Trim(SubField(first_attempt, '/', 1))) > 0, SubField(first_attempt, '/', 1)) as first_attempt_correct,
SubField(first_attempt, '/', 2) - SubField(first_attempt, '/', 1) as first_attempt_incorrect,
If(Len(Trim(SubField(second_attempt, '/', 1))) > 0, SubField(second_attempt, '/', 1)) as second_attempt_correct,
SubField(second_attempt, '/', 2) - SubField(second_attempt, '/', 1) as second_attempt_incorrect,
If(Len(Trim(SubField(third_attempt, '/', 1))) > 0, SubField(third_attempt, '/', 1)) as third_attempt_correct,
SubField(third_attempt, '/', 2) - SubField(third_attempt, '/', 1) as third_attempt_incorrect
Resident mainTable;

Questions:
LOAD
`question_id`,
`lender_offer_id`,answered_choice_id as answer_id;
SQL SELECT
`question_id`,
`lender_offer_id`,answered_choice_id
FROM rcdb.userquestion;

left join(Questions)
LOAD `question_id`,
`subject_id`;
SQL SELECT `question_id`,
`subject_id`
FROM rcdb.questions;

left join(Questions)
LOAD `question_text_id`,
`question_id`,
`language_id`,
`question_text`;
SQL SELECT `question_text_id`,
`question_id`,
`language_id`,
`question_text`
FROM rcdb.questiontext where language_id=1;

left join(Questions)
LOAD `answer_id`,
`question_id`;
SQL SELECT `answer_id`,
`question_id`
FROM rcdb.answers;

left join(Questions)
LOAD `answer_id`,
`answer_text`;
SQL SELECT `answer_id`,
`answer_text`
FROM rcdb.answertext where language_id=1;

Table_TEMP:
LOAD lender_offer_id,question_text,answer_text,
If(lender_offer_id= Previous(lender_offer_id),
RangeSum(Peek('RecordNumber'), 1), 1) as RecordNumber
;

LOAD
question_text, answer_text,question_id,answer_id,lender_offer_id
Resident Questions;
FinalTable:
LOAD Distinct lender_offer_id
Resident Table_TEMP;

FOR i = 1 to FieldValueCount('RecordNumber')

LET vField = FieldValue('RecordNumber', $(i));

Left Join (FinalTable)
LOAD lender_offer_id,
question_text as [Q$(vField)],
answer_text as [A$(vField)]
Resident Table_TEMP
Where RecordNumber= $(vField);

NEXT i
DROP Table Table_TEMP;

Try it and let us know the result.

Thanks,

Mohammed Mukram

Nikhil2725 · ‎2019-02-07

Hi Mohammed Mukram,
Thanks for clearing the doubt.....
Actually for each lender_offer_id there will be 10 questions and as well as the answers i.e Q1 to Q10 and A1 to A10
However Im getting only Q1,Q2,Q3 and A1,A2,A3......

Regards,

mdmukramali · ‎2019-02-07

Hi,

Well for that issue we need to check the data.
for that can you export the data into Excel or Qvd as share with us.

sunny_talwar · ‎2019-02-07

How about if you add an order by statement here

Table_Tmp:
LOAD lender_offer_id,
  question_text,
  answer_text,
  If(lender_offer_id = Previous(lender_offer_id), RangeSum(Peek('RecordNumber'), 1), 1) as RecordNumber;
LOAD question_text,
  answer_text,
  question_id,
  answer_id,
  lender_offer_id
Resident Questions
Order By lender_offer_id;

Related Topics