Generic load with Dynamic header column headers

[keerthanagowsi]

Hi All,

I have a requirement in which i have to pivot a table and bring the row in to column view.

I used generic load and achieved the required result. 

EX:

Name	Qtr-yr	Salary
ABC	Q4-2019	1000
ABC	Q1-2020	2000
ABC	Q2-2020	3000
DEF	Q4-2019	2500
DEF	Q1-2020	3500
DEF	Q2-2020	4500

Name	Q4-2019	Q1-2020	Q2-2020
ABC	1000	2000	3000
DEF	2500	3500	4500

But I have to show the column headers as below like

Name	Q	Q+1	Q+2
ABC	1000	2000	3000
DEF	2500	3500	4500

So that in the next quarter, even though the column Q4-2019 is not available in my data, it will not create any issue in my dashboard.

Can anyone help me with this requirement as i got stuck here  for more than 2 days.

Thanks in advance.

Regards,

keerthana

[MayilVahanan · Accepted solution]

HI @keerthanagowsi 

Might be, try like below before generic load

Sample:

LOAD * INLINE [
Name , Qtr-yr, Salary
ABC, Q4-2019, 1000
ABC, Q1-2020, 2000
ABC, Q2-2020, 3000
DEF, Q4-2019, 2500
DEF, Q1-2020, 3500
DEF, Q2-2020, 4500
];

Temp:
Load Distinct [Qtr-yr] Resident Sample;

Join(Sample)
LOAD *, if(RowNo()=1, 'Q', 'Q+'&(RowNo()-1)) as Qtr Resident Temp;

DROP Table Temp;

Based on above script, we will get Q, Q+1,Q+2 values as one column as 'Qtr'. After that, you can use ur script

[keerthanagowsi]

Thank you @MayilVahanan  , I will try this and update on the output.

[keerthanagowsi]

This solution works great 🙂 . Thankyou @MayilVahanan 

[keerthanagowsi]

Hi @MayilVahanan ,

Following the same topic, just a small alteration in the requirement shared, added Date column in the excel.

Name	QR	Date	Salary
ABC	Q4-2019	21/11/2020	1000
ABC	Q1-2020	21/11/2020	2000
ABC	Q2-2020	21/11/2020	3000
DEF	Q4-2019	21/11/2020	2500
DEF	Q1-2020	21/11/2020	3500
DEF	Q2-2020	21/11/2020	4500
ABC	Q1-2020	21/12/2020	500
ABC	Q2-2020	21/12/2020	2000
ABC	Q3-2020	21/12/2020	400
DEF	Q1-2020	21/12/2020	250
DEF	Q2-2020	21/12/2020	3600
DEF	Q3-2020	21/12/2020	4500

and the expected output is 

Name	Date	Q	Q+1	Q+2
ABC	21/11/2020	1000	2000	3000
DEF	21/11/2020	2500	3500	4500
ABC	21/12/2020	500	2000	400
DEF	21/12/2020	250	3600	4500

so as per the script you shared, is it possible to calculate the row number for each date.

For example, 

whenever the date changes, the row count should start again from 1. I think it is possible to achieve using For each loop, but I don't know how to apply it. Can you help me with this?

Thanks,

keerthana

[MayilVahanan]

Hi @keerthanagowsi 

Try like below

LOAD *, If(Name <> Previous(Name), 'Q', 'Q+'&(Peek(Num)+1)) as Qtr, If(Name <> Previous(Name) , 0, Peek(Num)+1) as Num INLINE [
Name , QR, Date, Salary
ABC, Q4-2019, 21/11/2020, 1000
ABC, Q1-2020, 21/11/2020, 2000
ABC, Q2-2020, 21/11/2020, 3000
DEF, Q4-2019, 21/11/2020, 2500
DEF, Q1-2020, 21/11/2020, 3500
DEF, Q2-2020, 21/11/2020, 4500
ABC, Q1-2020, 21/12/2020, 500
ABC, Q2-2020, 21/12/2020, 2000
ABC, Q3-2020, 21/12/2020, 400
DEF, Q1-2020, 21/12/2020, 250
DEF, Q2-2020, 21/12/2020, 3600
DEF, Q3-2020, 21/12/2020, 4500
];

DROP Field Num;

output:

[keerthanagowsi]

Hi,

It's Not working for my huge data