How to make day fields in this particular situation?

[#1]

I am having trouble with a proyect, i will give you an small example so i can explain it better.

I have this table:

Account	Date	Amount
1	01/02/2014	20
1	02/02/2014	30
1	03/02/2014	50
2	01/02/2014	60
2	02/02/2014	44
2	03/02/2014	77
3	01/02/2014	98
3	02/02/2014	10
3	03/02/2014	5

And i want to make fields for every day a month can have (31). In this example i have only three but the code would be the same.

So the final table i want to create by using the table i just posted would be like this:

Account	Period	Day1	Day2	Day3
1	201402	20	30	50
2	201402	60	44	77
3	201402	98	10	5

So for every period YYYYMM i have 31 different days. How can i make this happen in the script? I tried many ways but i can´t get to the result.

[tresesco]

Like this?

Load

          Day(DateField) as Day

[#3]

No, that would be too simple. That only makes the date field into the day, i need to transform the days into different columns, please see the example.

[MarcoWedel]

It's possible with a generic load, but you should consider creating a day-field like tresesco suggested and using this as a horizontal dimension in a pivot table.

This would visually create the table you are looking for while maintaining the possibility to select different day (as it still is a single field).

regards

Marco

[tresesco]

After creating the Day field, use a pivot table; take Day as third dimension; then pull Day up to column. Does this work?

[PrashantSangle]

Hi,

Please find enclosed file.

This will help you.

Regards,

[#7]

Thank you all for the help, but i need the output to be a simple table, it doesn´t work for me a pivot table because I have to use the data on another aplication, so the pivot table is not the answer.

The generic load is not good for me either because i need a final table as a result and the generic load creates seperate tables.

[MarcoWedel · Accepted solution]

Never give up, never surrender!

Generic load can do the trick:

tabTemp:
LOAD
  *,
  Date(MonthStart(Date), 'YYYYMM') as Period
FROM [http://community.qlik.com/thread/117550]
(html, codepage is 1252, embedded labels, table is @1);

tabTemp2:
Generic
LOAD
  Account,
  Period,
  'Day'&Day(Date) as Day,
  Amount
Resident tabTemp;

tabOutput:  
LOAD Distinct  
    Account,  
    Period   
Resident tabTemp;

DROP Table tabTemp;  

FOR i = 0 to NoOfTables()  
  TableList:  
  LOAD TableName($(i)) as Tablename AUTOGENERATE 1  
  WHERE WildMatch(TableName($(i)), 'tabTemp2.*');  
NEXT i  

FOR i = 1 to FieldValueCount('Tablename')  
  LET vTable = FieldValue('Tablename', $(i));  
  LEFT JOIN (tabOutput) LOAD * RESIDENT $(vTable);  
  DROP TABLE $(vTable);  
NEXT i  

DROP TABLE TableList;  

This script uses a generic load to create the DayX fields and a technique described by Rob Wunderlich to combine the generated tables into one: (Qlikview Notes: Use cases for Generic Load)

hope this helps

regards

Marco

[#9]

Thank you! That worked perfect!!